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

Question

I'm trying to write a Hibernate (JPA) query to select those parents where ALL their children match a property.

Let's make a pratical example...

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

How will the query look like? Thank you in advance!

Answer 1

Maybe something like this will work:

From Father f
where not exists (select c from f.children c where not c.hair = "BLONDE");

Just an idea...

Answer 2

Try this using the ALL see http://openjpa.apache.org/builds/1.1.0/docs/jpa_langref.html#jpa_langref_all_any:

select p from parent where 'blonde'=all(parent.children.haircolor)

Answer 3

This should also work and it looks a bit cleaner.

SELECT p from Parent p join p.children c where c.haircolor = 'blonde';