Learned Today: Joining with Hibernate

Hibernate (and NHibernate) is able to do a lot of different kind of joins. Unfortunately it can not do outer joins on properties that are not mapped as associations. My goal was to get the result of the following SQL-query with an outer join:

SELECT e.Name, d.Status, count(*) 

FROM event e
LEFT OUTER JOIN delivery d on e.Id = d.EventId

GROUP BY e.Name, d.Status

I hoped to realize the above query with Hibernate in HQL. Actually you can do joins between entities where no association is mapped:

SELECT new ReportDto(e.Name, d.Status, count(d))
FROM Delivery d, StatisticalEvent e where d.EventId = e.Id
GROUP BY d.EventId, e.Name, d.Status

... unfortunately this is an inner join.

You can do outer joins with Hibernate:
SELECT new ReportDto(e.Name, d.Status, count(d))
FROM Delivery d right outer join d.Event e
GROUP BY e.Name, d.Status

... unfortunately you have to have mapped the association between the two entities, since you have to feed a path to the join expression.

Read more: CLOSED-LOOP

Posted via email from .NET Info