Hibernate Criteria and row count restriction

Question

i have two entities named Parent and Child, linked in a one-to-many relationship. The Child entity has a boolean isStudent property.

How do i get, using the Hibernate Criteria API, all the Parent entities that have at least one Child with isStudent = true?

I was trying to use a Projection object to count all the parents that have at least one Child with the property correctly set, and then return those whose row count is greater than zero, like in the following piece of code (which doesn't work, though):

Criteria criteria = getCurrentSession().createCriteria(Parent.class);

criteria.setProjection(Projections.alias(Projections.rowCount(), "count"))
.add(Restrictions.gt("count", 0)).createCriteria("children")
.add(Restrictions.eq("isStudent", true));

Thanks for your help

Afflatus

Answer 1

This worked for me:

DetachedCriteria crit          = DetachedCriteria.forClass( Parent.class, "theparent" );
DetachedCriteria countSubquery = DetachedCriteria.forClass( Child.class , "child"     );

countSubquery
    .add( Property.forName("theparent.id").eqProperty( "parent.id" ) )
    .setProjection(Projections.count("id"));

crit.add(Subqueries.lt(Long.valueOf(0), countSubquery));

[Edit: fixed a bug a pointed out by @brabenetz]

Where there is a bidirectional relation between Parent and Child, i.e. the Child has a field "parent" It returns the Parents that have >0 children.

Configurationism

Answer 2

The Answer from RobAu is nearly what we needed. But there is a small bug in it: Instead of Subqueries.gt(..) you need Subqueries.lt(...)

crit.add(Subqueries.lt(Long.valueOf(0), countSubquery));

Scotney