JPA Query - JPQL to select parents that have ALL children with a property in a SET of values

Question

I'm trying write a JPQL query to return entities where all of that entity's children have a property that is in a set of values. It is similar to the following question, but where there are multiple possible values:

Hibernate query - How to select those parents that have ALL the children matching a value?

Here is a practical example adapted from the above question...

I want to select those fathers who have ALL their children who are either blond or redhead. If just one is black haired the father is not selected.

I've tried various adaptations of the answer to the above question like...

select p from parent where all(parent.children.haircolor) IN ('blonde','redhead')

or

select p from parent where parent.children.haircolor ALL IN ('blonde','redhead')

Didn't quite expect those to work, but worth a try. So far only one thing HAS worked...

select p from parent 
where 0 = (select count(c) from p.children c 
              where c.haircolor NOT IN ('blonde','redhead')
          )

I'd really prefer not to have to run such a count query for every row, but I'm not seeing a better mechanism. This doesn't totally surprise me since I can't quite think of any other way to write this in plain SQL, but I'm not a guru there either. Is there a more efficient way to accomplish this?

Answer 1

You try to use JPQL path expressions over what looks like a collection property - you can't do that. Instead do a join like this:

SELECT p FROM Parent p JOIN p.children c WHERE c.haircolor IN :hairColorCollection

Above, Parent is assumed to be an entity with a collection valued property children of which every target entity has a single valued haircolor property. :hairColorCollection is a parameter which should be set to a collection object before executing the query.