Search for elements with a set containing all the elements of a given set with jpql

Question

I want to find elements that contain all the tags in their tags.

Here are the simplified classes:

@Entity class Item { @ManyToMany var tags: java.util.Set[Tag] = new java.util.HashSet[Tag]() } @Entity class Tag { @ManyToMany(mappedBy="tags") var items: java.util.Set[Item] = new java.util.HashSet[Item] } 

If I try to do it

 select distinct i from Item i join i.tags t where t in (:tags) 

I get items containing any of the specified tags. This is not surprising, but I want the elements to contain all the tags. So I'm trying to do it the other way around:

 select distinct i from Item i join i.tags t where (:tags) in t 

I get an error org.hibernate.exception.SQLGrammarException: arguments of row IN must all be row expressions . It works if tags contains only one tag, but with an error it is larger.

How can I express this in JPQL?

Answer 1

The trick is to use a counter:

 select i from Item i join i.tags t where t in :tags group by i.id having count(i.id) = :tagCount 

Answer 2

I had the same problem as you. I used reductio ad absurdum:

 select distinct i from Item i where i not in (select i2 from Item i2 join i2.tags t where t not in :tags)