Java – how to combine left join and where clauses with jpql?

I have two JPA entities:

>Attached table (including appointment list) > appointment (including date field: date resdate)

My goal is to retrieve the matching date parameter (planningdate) only when retrieving all timesheets, regardless of whether there is a reservation on 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 is there no booking schedule on this date, despite my left join?

Perhaps, like a local query, when combined with a where clause, the left join looks like an inner join

So how do I change the query to meet my requirements? I didn't find a specific function in jpql

Solution

Ah, this is indeed a classic of JPA

For a 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 stated not to be worked by hibernate / JPA personnel, but it does The generated SQL is also very effective I would be most impressed if someone could explain why this job is I learned this pattern years ago, but I can't remember it for my life

One note: there is a situation that does not work properly, and in this case, this schedule has no reservations In this case, the only answer I can provide is to wrap your query in the try / catch of "noresultexception" and re query without the where clause (obviously, if there is no reservation, there is no reservation planningdate on the resdate)