CODEWITHSUNDEEP
javahibernatejpajpql
deamon
deamon

Reputation: 92509

Exclude items with JPQL "not in"

I want to exclude Items tagged with certain tags with the following JPQL query:

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

It works if an item has only a single tag and this tag is in the excludedTags list. But if there is any other tag on that item, it gets still selected!

The relevant part of the model:

@Entity
class Tag {
  @ManyToMany(mappedBy="tags")
  var items
}

@Entity
class Item {
  @ManyToMany
  var tags
}

How can I exclude items that have any excluded tag with JPQL?

Upvotes: 0

Views: 2005

Answers (1)

JB Nizet
JB Nizet

Reputation: 692003

The query should be something like this instead:

select distinct i from Item i where not exists (
    select t from Item i2 
    join i2.tags tag
    where i2.id = i.id
    and tag.id in :excludedTags)

Upvotes: 1

Related Questions