14

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.

Mik378
  • 21,881
  • 15
  • 82
  • 180
  • Yes, I noticed that. I also know that with a native query, the condition can be put on the "ON" clause of the LEFT JOIN. But it seems like it's not possible in JPQL; that's why I ask :) – Mik378 Feb 14 '12 at 00:30
  • You're right, the question is not clear. I'm reediting it. – Mik378 Feb 14 '12 at 00:33
  • Have you tried `LEFT OUTER JOIN` not sure if the syntax allows or but some languages use that? – dann.dev Feb 14 '12 at 00:52
  • Yes, I tried it. Same effect. – Mik378 Feb 14 '12 at 00:55
  • How are your two entities related? If there is no existing relationship (eg `@JoinColumn( name="mycolumn" )` I don't think you can use the join. – dann.dev Feb 14 '12 at 00:59
  • On schedule entity there's a oneToMany related to reservations collection with forced eager fetch. And on reservation entity, there's a manyToOne on schedule field. Of course, those fields are well mapped to the good columns' name. – Mik378 Feb 14 '12 at 01:08

3 Answers3

19

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)

Alex Taylor
  • 7,128
  • 2
  • 26
  • 22
  • 4
    This will only work if `resDate` is a non-NULL column. If it were nullable, then you might get wrong results. You could use the Hibernate-specific `WITH` instead, like `LEFT JOIN s.reservations r WITH r.resDate = :planningDate`, which will yield the correct SQL statement with an additional clause in the JOIN, such as `from t_schedule s left join t_reservations r on r.schedule_id = s.id and r.resDate='some date'`. – Arjan Jan 15 '13 at 10:37
  • I tried the 'WITH' keyword and it does not work since I'm lazy loading my bags. I'll have to look into 'filters' – Dan Mar 13 '14 at 17:12
  • @Arjan, can you provide a simple Filters example, if you know one? – Dan Mar 13 '14 at 22:34
3

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 > ?

zealot
  • 237
  • 1
  • 4
1

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.

Mik378
  • 21,881
  • 15
  • 82
  • 180
  • (Quite late, but see my comment about `WITH` at Chaos' answer. Hibernate-specific though.) – Arjan Jan 15 '13 at 13:46