I want to retrieve all Foo objects and for every Foo object the number of associated Bar objects (zero or more) from the database. I want to do this in one single query and I don't want to fetch lists of Bar objects just to get their sizes.
The following works, but obviously only returns Foo objects that have matching Bar objects:
@NamedQuery(name = "Foo.findAndCountBars", query = "
SELECT new com.test.MyResultContainer(f, COUNT(b.id))
FROM Foo f, Bar b
WHERE f.uniqueKey = b.uniqueKey
GROUP BY f.id
")
Notice that Foo and Bar are not connected via primary keys. The following is what I want to achieve (without an explicit relationship such as @OneToMany in Foo):
@NamedQuery(name = "Foo.findAndCountBars", query = "
SELECT new com.test.MyResultContainer(f, COUNT(b.id))
FROM Foo f LEFT JOIN Bar b
ON f.uniqueKey = b.uniqueKey
GROUP BY f.id
")
I tried different syntax variations, but basically Eclipse tells me that b.id is unknown because Bar is not in the FROM clause. How can I do this?
