Counting the number of objects that meets a certain criteria
- by Candy Chiu
The title doesn't tell the complete story. Please read the message.
I have two objects: Adult and Child. Child has a boolean field isMale, and a reference to Adult. Adult doesn't reference Child.
public class Adult {
long id;
}
public class Child {
long id;
boolean isMale;
Adult parent;
}
I want to create a query to list the number of sons each adult has including adults who don't have any sons. I tried:
Query 1
SELECT adult, COUNT(child) FROM Child child
RIGHT OUTER JOIN child.parent as adult
WHERE child.isMale='true'
GROUP BY adult
which translates to sql
select
adult.id as col_0_0_,
count(child.id) as col_1_0_,
... {omit properties}
from
Child child
right outer join
Adult adult
on child.parentId=adult.id
where
child.isMale = 'true'
group by
adult.id
Query 1 doesn't pick up adults that don't have any sons.
Query 2:
SELECT adult, COUNT(child.isMale) FROM Child child
RIGHT OUTER JOIN child.parent as adult
GROUP BY adult
translates to sql:
select
adult.id as col_0_0_,
count(child.id) as col_1_0_,
... {omit properties}
from
Child child
right outer join
Adult adult
on child.parentId=adult.id
group by
adult.id
Query 2 doesn't have the right count of sons. Basically COUNT doesn't evaluate isMale.
The where clause in Query 1 filtered out Adults with no sons.
How do I build a HQL or a Criteria query for this use case?
Thanks.
