Hibernate criteria with restrictions on children

Question

I have a Hibernate criteria call that I want to execute in one SQL statement. What I'm trying to do is select instances of Parent that have Children with a property in a range of values (SQL IN clause), all while loading the children using an outer join. Here's what I have so far:

 Criteria c = session.createCriteria(Parent.class);

 c.createAlias("children", "c", CriteriaSpecification.LEFT_JOIN)
          .setFetchMode("c", FetchMode.JOIN)
          .add(Restrictions.in("c.property", properties));

 c.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

 return c.list();

Here's some sample data:

Parent
Parent ID
A
B
C

Children
Child ID    Parent ID   property
...         A           0
...         A           2
...         A           7
...         B           1
...         C           1
...         C           2
...         C           3

What I want to do is return the parents and ALL their children if one of the children has a property equal to my bind parameter(s). Let's assume properties is an array containing {2}. In this case, the call will return parents A and C but their child collections will contain only element 2. I.e. Parent[Children]:

A[2] & C[2]

What I want is:

A[0, 2, 7] & C[1, 2 3]

If this is not a bug, it seems to be a broken semantic. I don't see how calling A.getChildren() or C.getChildren() and returning 1 record would ever be considered correct -- this is not a projection. I.e. if I augment the query to use the default select fetch, it returns the proper children collections, albiet with a multitude of queries:

  c.createAlias("children", "c").add(
      Restrictions.in("c.property", properties));

Is this a bug? If not, how can I achieve my desired result?

Answer 1

Criteria c = session.createCriteria(Parent.class);

c.createAlias("children", "children");
c.add(Restrictions.in("children.property", properties));

c.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

return c.list();

Answer 2

I would start the Criteria with the child class. You'll get a list with all the children, and then you can iterate and get the parent for each children.

Answer 3

This can be done in work around way.

Criteria c1 = session.createCriteria(Child.class);
c1.add(Restrictions.in("property", properties));
c1.setProjection( Projections.distinct( Projections.property( "parentId" ) ) );
List<Integer> parentIds = c1.list();

Criteria c2 = session.createCriteria(Parent.class);
c2.createAlias("children", "children");
c2.add(Restrictions.in("id", parentIds));
return c2.list();

Answer 4

getChildren() is just the name of the getter/setter, your query will determine how the objects get populated.

I'm going to guess here that the first part spits out

SELECT * FROM Parent 
INNER JOIN Child c ON ... 
WHERE c.property in (x,y,z) 

which doesn't get you what you want. What'd you'd want to do if you were writing this in raw SQL is this:

SELECT * FROM Parent  
WHERE ParentID IN (SELECT DISTINCT parentID FROM Child WHERE  c.property in (x,y,z))

rearranging your criteria appropriately might do the trick if the last one isn't producing this query. (Could you also post what hibernate is generating for each?)