Spring Data JPA Query中按子对象过滤时出错 [英] Error in filtering by child object in Spring Data JPA Query

问题描述

我的代码结构如下所示。

My code structure looks like the following.

文章：

@Entity
public class NewsArticle{

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

    [Other class properties such as title, publisher, publishedDate, etc.]        

    @OneToMany(mappedBy = "article")
    private Set<UserReadNewsArticle> userReadNewsArticles = new HashSet<>();

    [Getters and Setters]
}

文章读者用户：

@Entity
public class UserReadNewsArticle {

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

    private Long readAccountId;    
    private Long readArticleId;           

    @JsonIgnore
    @ManyToOne
    private Account account;

    @JsonIgnore
    @ManyToOne
    private NewsArticle article;

    [Getters and Setters]


}

账户：

@Entity
public class Account {

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

    [Other class properties]

    @OneToMany(mappedBy = "account")
    private Set<UserReadNewsArticle> userReadNewsArticles = new HashSet<>();

    [Getters and Setters]
}

我想要在我的NewsArticleRepository中有一个查询方法来获取用户的所有阅读新闻文章。

I want to have a query method in my NewsArticleRepository to get all the Read News Articles for a user.

public interface NewsArticleRepository extends PagingAndSortingRepository<NewsArticle, Long>{

    Collection<NewsArticle> findByUserReadNewsArticlesReadAccountId(Long readAccountId);

}

此方法效果很好。但是，如何编写Spring Data JPA查询/方法来获取未读新闻文章给用户。我试过的是以下内容。

This method works great. But how can I write a Spring Data JPA Query/Method to get the "Unread News Articles for a user". What I have tried is the following.

Collection<NewsArticle> findByUserReadNewsArticlesReadAccountIdNot(Long readAccountId);

这个确实返回了其他用户已读过的文章列表。但我的要求是获取所有未读的新闻文章。我已经浏览了 Spring Data JPA文档但是未能想出一个更容易的灵魂。我怎样才能克服这个问题？或者我做错了什么？

This one does return a list of articles which have been read by other users. But my requirement is to get all the unread news articles. I have gone through Spring Data JPA Documentation but failed to come up with an easier soultion. How can I overcome this issue? Or am I doing something wrong?

推荐答案

您可以通过使用带有子查询的JPQL查询来实现结果：

You could achieve your result by using a JPQL query with also a subquery:

public interface NewsArticleRepository extends PagingAndSortingRepository<NewsArticle, Long> {

    @Query("SELECT n FROM NewsArticle n WHERE n NOT IN "
            + "(SELECT ur.article FROM UserReadNewsArticle ur JOIN ur.account a WHERE a.id = :readAccountId)")
    Collection<NewsArticle> findByUserReadNewsArticlesReadAccountIdNotIn(@Param("readAccountId") Long readAccountId);

}

http：// localhost：8080 / newsArticles / search / findByUserReadNewsArticlesReadAccountIdNotIn？readAccountId = 1

首先从当前用户那里获取阅读清晰度，然后将它们从整篇文章列表中排除。

So first get the read articels from the current user and then exlude them from the whole article list.

我不认为spring数据能够得到你一样，因为子查询是绝对需要的。如果我错了，有人可以纠正我。

I don't think that spring data is able to get you the same, since a subquery is definitetly needed. If I'm wrong, somebody can correct me.

这篇关于Spring Data JPA Query中按子对象过滤时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！