In hibernate I have entities A and B. There's is a one-to-many from A to B (A -< B).
I need to write a query where I can select A (plus criteria) plus an additional column that reflects the existence, or not, of any B for each A (with some additional criteria on B)
So there might be one A with no Bs and another A with 2 Bs. In this case I want 2 rows back, one for each A with "no" for the first and "yes" for the second. I am not fussed what "no" and "yes" actually are, just so I can distinguish.
If I use a left outer join I will get more than one row per A in the many B case.
How I can I write this in HQL? Do I perhaps need a group by (but you can't group by entities I think)?
Additional: I also need to restrict the Bs that are relevant based on some conditions.
One possible solution is to use
select a, case when (a.bs is empty) then false else true end from A a
group by can be used as well, but, as you noticed, depending on your DBMS you may need to enumerate all properties of your entity in
group by clause.