Hibernate criteria query to match against all child collection elements

Question

This question is very similar to this one but the responses were minimal to that question.

I have a parent class with a Set of child entities. The child entities are just a wrapper for a string and live in a different table to the parent entity. I want to have a criteria query that returns the parent entities when all the members of the set of child entities return true to a condition. This condition is matching against one of a list of strings. Here's where I am:

Criteria c = criteria();
Criteria ands = c.createCriteria("ands");
Disjunction dis = Restrictions.disjunction();
for (String value : values) {
    dis.add(Restrictions.like("value", "%" + value + "%"));
}
ands.add(dis);
return list(c);

"ands" is the set of entities with a "value" field that is a string. "criteria()" creates a criteria for the parent class. "list()" just calls criteria.list();

This is just matching against any of the elements, rather than all.

Hope this makes sense. Any help much appreciated.

Answer 1

As a theoretical exercise, you can do something like this:

Criterion condition = ...;

Criteria c = s.createCriteria(Parent.class, "p");
DetachedCriteria dc = DetachedCriteria.forClass(Parent.class, "p2")
    .createCriteria("ands", "c")
    .add(Restrictions.not(condition))
    .add(Property.forName("p.id").eqProperty("p2.id"))
    .setProjection(Projections.id());

c.add(Subqueries.notExists(dc));

However, this approach is not good for practical use because it requires extra join (due to absence of in elements clause in Criteria API). Also note that it uses double negation (SELECT ... WHERE NOT EXISTS (SELECT ... WHERE NOT <condition>)), so it can have problems with NULLs.

EDIT: In HQL it can be written like this:

from Parent p
where not exists (select c from p.ands c where not <condition>)

or

from Parent p
where not exists (select c from Child c 
    where not <condition> and c in elements(p.ands))

But, as far as I understand, both queries can't be expressed in Criteria API (you can't write from p.ands in subquery and can't use in elements). So, in Criteria API you have to use additional join (note 2 Parents):

from Parent p
where not exists (select c from Parent p2 join p2.ands c 
    where not <condition> and p = p2)