I have a following entity structure.
Venue -- n:1 -- Schedule -- 1:n -- Event
I have a query to get counts of all schedule in Venues:
SELECT v, count(s.event) FROM Venue v
LEFT JOIN i.schedule s
GROUP BY i.id ORDER BY count(s.event) asc;
The problem is that this query will never output venues, that have zero events. The problem is with Hibernate, which generates following:
select ..., count(schedule4_.event_id) as col_7_0_
from Venue venue0_
left outer join event_schedule schedule4_ on venue0_.id=schedule4_.venue_id,
Event event5_
where schedule4_.event_id=event5_.id and ...
You can see, that Hibernate tries to join Event
on Schedule
even though I did not request that. Obviously if the Schedule
does not exist, joining Event
does not make any sense.
(I simpified the query, if there is some typo, it should not have any impact on the problem).
Try with the following query:
select v.id, count(e.id) from Venue v
left join v.schedule s
left join s.events e
group by v.id order by count(e.id) asc;