JPA: find all articles containing a common set of tags

Question

I have the following objects:

@Entity public class Article{ @Id private String id; @ManyToMany(cascade = CascadeType.PERSIST) private Set<Tag> tags; //non-relevant code } @Entity public class Tag{ @Id private String id; @Basic @Column(nullable = false, unique = true, length = 32) private String name; //non-relevant code } 

How can I effectively find all Article entities that have a common set tag?

The naive approach is to find all the articles that belong to each tag , and then return the intersection all sets of articles. Something like:

 public Set<Article> findByTags(Set<Tag> tags){ Set<Article> result = new HashSet<>(); if(tags.isEmpty()){ return result; } Iterator<Tag> i = tags.iterator(); result.addAll(i.next().getArticles()); while(i.hasNext() && !result.isEmpty()){ result.retainAll(i.next()); } return result; } 

My question is: " Is there a more efficient way to do this that doesn’t require fetching all the articles from a database like this? Perhaps using a JPQL query or using CriteriaBuilder (I have never used it before)"

Answer 1

 select a from Article a where :numberOfTags = (select count(distinct tag.id) from Article a2 inner join a2.tags tag where tag in :tags and a = a2) 

This mainly takes into account article tags, which are tags in the adopted tag set, and if the number of such tags is eaqual to the size of the received tag set (this means that all tags in the set are article tags), returns the article.