Counting the number of objects matching certain criteria

Question

The name does not tell the full story. Read the message.

I have two objects: Adult and Child. The child has a boolean field isMale and a link to Adult. The adult does not refer to the 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 that every adult has, including adults who do not have sons. I tried:

Request 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

In query 1, adults who do not have sons do not appear.

Request 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

Request 2 does not have the correct number of sons. Basically COUNT does not evaluate isMale.
The where clause in Query 1 filtered out adults without sons.

How to create an HQL or Criteria query for this use case?

Thank.

Answer 1

child.isMale='true' (isMale is NULL), :

SELECT adult, COUNT(child) 
FROM Child child RIGHT OUTER JOIN child.parent as adult 
WHERE child IS NULL OR child.isMale='true'  
GROUP BY adult