JPA：查找具有一组通用标签的所有文章 [英] JPA: Find all articles that have a common set of tags

问题描述

我有以下实体：

@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
}

怎么能我有效地找到所有 Article 具有共同设置标签的实体？

How can I efficiently find all Article entities that have a common set of tags?

天真的方法是查找属于每个标签的所有文章，然后返回交叉点所有文章集。类似于：

The naive approach is to find all the articles that belong to each tag and then return the intersection of all the article sets. 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;
}

我的问题是 是否有更有效的方式这样做，这不需要从数据库中获取所有文章，如此吗？ 可能通过JPQL查询或使用 CriteriaBuilder （我之前从未使用过它）

My question is "Is there more efficient way of doing this, that does not require to fetch possibly all the articles from the DB, like this one ? Maybe through a JPQL query or using the CriteriaBuilder (I've never used it before)"

推荐答案

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 basically counts the tags of the articles that are tags in the accepted set of tags, and if the number of such tags is eaqual to the size of the accepted set of tags (meaning that all tags in the set are tags of the article), returns the article.

这篇关于JPA：查找具有一组通用标签的所有文章的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！