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)