How to SELECT WHERE IN ALL elements of list

Question

I have an entity Entry which has following relations:

@Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ManyToMany(mappedBy = "entryList", cascade = {CascadeType.ALL} )
    private List<Tag> tags = new LinkedList<>();

This SELECT statement selects all Entry where in the relation tags is at least one element from the list list:

SELECT m FROM Entry m JOIN m.tags tags WHERE tags IN :list;

But what I want is a SELECT statement to select all Entry where all elements of list must be in relation tags?

Answer 1

I think you need to use a subquery and a count,

Select e from Entry e where (Select count(t) from Tag t, Entry e2 join e2.tags t2 where t2 = t and e = e2 and t.id in (:ids)) = :size

Where ids are the ids of the tags (you may be able to use the objects as well), and :size is the size of the tags collections.

If you tag has an inverse m-m to entry you can use,

Select e from Entry e where (Select count(t) from Tag t join t.entries e2 where e = e2 and t.id in (:ids)) = :size