问题描述:

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.

网友答案:

Condition child.isMale='true' is false for adults without children (isMale is NULL), therefore you should handle this case:

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
相关阅读:
Top