How to combine LEFT JOIN and Where clause with JPQL?

Question

I have two JPA entities :

• Schedule (containing a list of reservations)
• Reservation (containing a Date field : Date resDate)

My goal is to only retrieve reservations matching a date parameter (planningDate) while retrieving all schedules no matter if the reservation exists or not at this given date.

So I wrote :

SELECT s FROM Schedule as s LEFT JOIN s.reservations as r WHERE r.resDate = :planningDate order by s.startHour

Why aren't schedules, without reservations on this date, retrieved despite my LEFT JOIN ?

Probably, like native queries, LEFT JOIN looks like INNER JOIN when combining with a WHERE clause.

So, how could the query be changed to fulfill my requirement ? I haven't found a specific feature in JPQL.

Answer 1

Ah, this is indeed a classic in JPA. The following answer I provide - I cannot explain exactly why it works, but I can solve this for 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 is null" part)

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

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

Answer 2

In EclipseLink 2.5 (Glassfish 4.0, Oracle 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 decart multiplication):

FROM requests t0 , users t1 WHERE ((t1.ID = t0.assignee_id)) ORDER BY CASE WHEN (t0.assignee_id IS NULL) THEN ? ELSE t1.ID END DESC) a WHERE ROWNUM <= ?) 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 part (used left join):

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

Answer 3

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

Reminding the initial goal :

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

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

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

If you see a better solution, I would appreciate.