Andrei V Andrei V - 1 month ago 14
SQL Question

Querydsl: how to make left join by column

Im trying to match this SQL query in querydsl

SELECT tr.* FROM test.TRIP_REQ tr left outer join test.ADDR_BOOK ab on tr.REQ_USERID=ab.USER_ID


I know how to make left join query if you join into identity column but struggle to make it work with joining on 2 alternative columns. tr.REQ_USERID and ab.USER_ID are not identity columns

This is my querydsl:

QTripReq qTripReq = QTripReq.tripReq;
QAddressBook qABook = QAddressBook.addressBook;
JPAQuery query = new JPAQuery(entityManager);
query.from(qTripReq).leftJoin(qABook).on(qTripReq.requestorUser.id.eq(qABook.user.id)).list(qTripReq);


This throws error:


Path expected for join! [select tripReq from com.TripReq tripReq left join ADDR_BOOK addressBook with tripReq.requestorUser.id = addressBook.user.id where tripReq.assignedCompany.id = ?1]


Thank you.

Answer

You need to add a target entity path to leftJoin(), so that

QTripReq qTripReq = QTripReq.tripReq;
QAddressBook qABook = QAddressBook.addressBook;
JPAQuery query = new JPAQuery(entityManager);
query.from(qTripReq).leftJoin(qTripReq.addressBook, qABook).on(qTripReq.requestorUser.id.eq(qABook.user.id)).list(qTripReq);

Take a look on Using joins section in docs.

Comments