Finding items with a set containing all elements o

问题:

I want to find the items that contain all the given tags in their tags set.

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 it like this

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

I get the items that contain any of the given tags. That is not surprising, but I want Items that contain all of the given tags. So I try it the other way around:

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

I get the error message org.hibernate.exception.SQLGrammarException: arguments of row IN must all be row expressions. It works if tags contains only a single tag, but it fails with more than that.

How can I express this in JPQL?

回答1:

The trick is to use a count:

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

回答2:

I just had the same problem as you. I used the 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)