[ACCEPTED]-How to combine LEFT JOIN and Where clause with JPQL?-left-join

Accepted answer
Score: 18

Ah, this is indeed a classic in JPA. The 18 following answer I provide - I cannot explain 17 exactly why it works, but I can solve this for 16 you

Short answer, try:

SELECT s FROM Schedule as s LEFT JOIN s.reservations as r WHERE 
(r.resDate is null or r.resDate = :planningDate) order by s.startHour

(The key here is the "r.resDate 15 is null" part)

Long answer: This is explicitly said 14 not to work by the hibernate/JPA people, but 13 it does. The generated SQL is also quite 12 efficient. If anyone can explain why this 11 works, I will be most impressed. I learned 10 this pattern years ago, but can't for the 9 life of me remember where.

One note: There 8 is one case where this will not work, and that 7 is in the instance where there are no reservations 6 for this schedule. In this case, the only 5 answer I can provide is that you can wrap 4 your query in a try/catch for "NoResultException", and 3 query again without the where clause (obviously 2 if there are no reservations, there are 1 no reservations with a resDate on planningDate)

Score: 3

In EclipseLink 2.5 (Glassfish 4.0, Oracle 9 11g XE database):

Doesn't work (no alias assigned to r.assignee):

from Request r left join r.assignee 
order by case when r.assignee.id is null then 0 else r.assignee.id end desc

Generated query part (used 8 decart multiplication):

FROM requests t0 , users t1 WHERE ((t1.ID = t0.assignee_id)) ORDER 7 BY CASE WHEN (t0.assignee_id IS NULL) THEN 6 ? ELSE t1.ID END DESC) a WHERE ROWNUM 5 <= ?) WHERE rnum > ?

Works (added a alias to r.assignee):

from Request r left join r.assignee as a
order by case when a.id is null then 0 else a.id end desc

Generated query 4 part (used left join):

FROM requests t1 LEFT OUTER JOIN users t0 ON (t0.ID = t1.assignee_id) ORDER 3 BY CASE WHEN (t0.ID IS NULL) THEN ? ELSE 2 t0.ID END DESC) a WHERE ROWNUM <= ?) WHERE 1 rnum > ?

Score: 1

Finally I think there's case where the concept 22 of LEFT JOIN in JPA cannot apply.

Reminding 21 the initial goal :

Retrieve all schedules 20 whatever happens while only populate reservations 19 collections (in these respectives schedules 18 so) when matching a given planningDate.

Indeed, even 17 if I manage to retrieve schedules whose 16 reservations don't match my criterias, those 15 schedules will, anyway, reload their collections 14 of reservations respectively if those ones 13 are declared with a fetch type as "eager", and 12 so no effect of the restriction clause to 11 a precised "planningDate". It is a behaviour 10 exactly similar to select all reservations 9 of all schedules without ANY others restrictions.

So 8 the most simple adapted solution to my issue 7 in JPA would be to make 2 requests : select 6 schedules first and select matching reservations 5 to planningDate secondly and independently. Thus, results 4 could be regrouped into one list and be 3 returned. Drawbacks are that reservations 2 collections are load to times.

If you see 1 a better solution, I would appreciate.

More Related questions