使用Spring Data JpaRepository按计数排序

Question

我正在使用Spring Data JpaRepository，我发现它非常易于使用。实际上，我需要所有这些功能-分页、排序、过滤。不幸的是，有一件小小的令人讨厌的事情似乎强迫我回到使用普通的JPA。

我需要按关联集合的大小进行排序。例如，我有：

@Entity
public class A{
    @Id
    private long id;
    @OneToMany
    private List<B> bes;
//boilerplate
}

我必须按 bes.size() 进行排序。

是否有一种方法可以自定义排序，同时仍然利用分页、过滤和其他Spring Data优秀的功能呢？

- makasprzak

Answer 1

我使用以下提示和灵感解决了这个谜题：

1. 通过使用@Query注释限制结果集 ，作者为Koitoer
2. 如何在JPA中按count()排序，作者为MicSim
3. 通过自己进行全面的实验。

我之前不知道关于 spring-data 的第一件最重要的事情是，即使使用@Query自定义方法，也可以通过简单地将Pageable对象作为参数传递来创建分页查询。这是一项非常强大的功能，尽管它并不明显，但它本应该由spring-data文档明确说明。

好的，现在是第二个问题 - 如何在JPA中按相关集合的大小对结果进行排序？我设法得出以下JPQL：

select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a

其中AwithBCount是一个类，用于将查询结果实际映射到该类：

public class AwithBCount{
    private Long bCount;
    private A a;

    public AwithBCount(Long bCount, A a){
        this.bCount = bCount;
        this.a = a;
    }
    //getters
}

我很兴奋，因为现在我可以像下面这样简单地定义我的代码库

public interface ARepository extends JpaRepository<A, Long> {
    @Query(
        value = "select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a",
        countQuery = "select count(a) from A a"
    )
    Page<AwithBCount> findAllWithBCount(Pageable pageable);
}

我赶紧尝试了一下我的解决方案。完美-页面已经返回，但是当我尝试按bCount排序时，我感到失望。原来，由于这是一个ARepository（而不是AwithBCount repository），spring-data会尝试在A中查找bCount属性，而不是AwithBCount。所以最终我得到了三个自定义方法：

public interface ARepository extends JpaRepository<A, Long> {
    @Query(
        value = "select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a",
        countQuery = "select count(a) from A a"
    )
    Page<AwithBCount> findAllWithBCount(Pageable pageable);

    @Query(
        value = "select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a order by bCount asc",
        countQuery = "select count(a) from A a"
    )
    Page<AwithBCount> findAllWithBCountOrderByCountAsc(Pageable pageable);

    @Query(
        value = "select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a order by bCount desc",
        countQuery = "select count(a) from A a"
    )
    Page<AwithBCount> findAllWithBCountOrderByCountDesc(Pageable pageable);
}

...而且还有一些针对服务级别的附加条件逻辑（可以通过抽象仓库实现封装）。因此，虽然不是非常优雅，但这样做很有用——通过这种方式（拥有更复杂的实体），我可以按其他属性进行排序、筛选和分页。

- makasprzak

---

非常好的使用JPQL，解决问题的方式非常有趣。感谢您详细地分享了它。 - Koitoer

我不知道这里设置了 c 的位置 AwithBCount(count(b.id) as bCount,c)，而且你不需要写 count(b.id) as bCount，只写 count(b.id) 就可以了。 - azerafati

这个解决方案对我不起作用 - 是否可能返回与 A 类型不同的 Page ？！ 在尝试此解决方案时，我收到了一个错误，指出 AwithBCount 是未映射的实体。 - miran

这个解决方案对我不起作用，提到AwithBCount不是受管理的类型。 - Yogesh

我认为这个解决方案在Spring Boot 2.x中不能使用。实现似乎已经改变：一个存储库只能有返回与其链接的实体或数字（长/整数）的方法。由于没有AwithBCount的存储库，因此会出现此错误。与存储库相关的所有实体都存储在某种映射中：实体->该实体的存储库；而AwithBCount不是实体。解决方案是编写一个专用方法，在控制器或不是存储库的服务中执行您编写的JPQL。 - Cyril Gambis

Answer 2

一个比原始解决方案更简单且具有额外好处的选择是创建聚合数据的数据库视图，并通过 @SecondaryTable 或 @OneToOne 将您的实体与之链接。

例如：

create view a_summary_view as
select
   a_id as id, 
   count(*) as b_count, 
   sum(value) as b_total, 
   max(some_date) as last_b_date 
from b 

使用@SecondaryTable

@Entity
@Table
@SecondaryTable(name = "a_summary_view", 
       pkJoinColumns = {@PrimaryKeyJoinColumn(name = "id", referencedColumnName= "id")})
public class A{

   @Column(table = "a_summary_view")
   private Integer bCount;

   @Column(table = "a_summary_view")
   private BigDecimal bTotal;

   @Column(table = "a_summary_view")
   private Date lastBDate;
}

现在，您可以只参考实体A对其进行排序、过滤、查询等操作。

作为额外的优势，在您的领域模型中有些数据可能很昂贵且难以在内存中计算，例如所有客户订单的总价值，而无需加载所有订单或返回单独的查询。

- Alan Hay

Answer 3

感谢 @Alan Hay，这个解决方案对我很有效。我只需要设置 @SecondaryTable 注释的 foreignKey 属性，一切都可以正常工作（否则 Spring Boot 会尝试向 id 添加外键约束，这会导致 SQL 视图出错）。

@SecondaryTable(name = "product_view",
            pkJoinColumns = {@PrimaryKeyJoinColumn(name = "id", referencedColumnName = "id")},
            foreignKey = @javax.persistence.ForeignKey(ConstraintMode.NO_CONSTRAINT))

- Theo Martinez

Answer 4

我对Spring Data不是很了解，但是对于JPQL，如果要按关联集合的大小对对象进行排序，可以使用以下查询：

Select a from A a order by a.bes.size desc

- thanh ngo

Answer 5

我使用了nativeQuery来按照另一个表中的记录数量进行排序，分页工作正常。

 @Query(value = "SELECT * FROM posts where posts.is_active = 1 and posts.moderation_status = 'ACCEPTED' " +
                "group by posts.id order by (SELECT count(post_id) FROM post_comments where post_id = posts.id) desc",
                countQuery = "SELECT count(*) FROM posts",
                nativeQuery = true)
        Page <Post> findPostsWithPagination(Pageable pageable);

- Raf

---

你的回答可以通过提供更多支持信息来改进。请编辑以添加进一步的细节，例如引用或文档，以便他人可以确认你的答案是正确的。您可以在帮助中心中找到有关如何编写良好答案的更多信息。 - Axel Meier

Answer 6

您可以使用在SELECT子句中找到的属性名称作为排序属性：

@Query(value = "select a, count(b) as besCount from A a join a.bes b group by a", countQuery = "select count(a) from A a")
    Page<Tuple> findAllWithBesCount(Pageable pageable);

现在你可以根据属性besCount排序：

findAllWithBesCount(PageRequest.of(1, 10, Sort.Direction.ASC, "besCount"));

- Flore

Answer 7

对于SpringBoot v2.6.6，如果您需要在使用@ManyToOne时使用pageable与子侧字段，则接受的答案无效。

对于接受的答案：

• 您可以返回具有静态查询方法的新对象，该方法必须包括order by count(b.id)
• 而且order by bCount也不起作用。

请使用@AlanHay的解决方案，它有效，但您不能使用基本字段并更改外键约束。例如，将long更改为Long。因为：

当保存新实体时，Hibernate会认为必须将记录写入具有零值的辅助表。（如果使用原始类型）

否则，您将收到异常：Caused by: org.postgresql.util.PSQLException: ERROR: cannot insert into view "....view"

这是一个例子：

@Entity
@Table(name = "...")
@SecondaryTable(name = "a_summary_view,
        pkJoinColumns = {@PrimaryKeyJoinColumn(name = "id",
                referencedColumnName= "id")},
foreignKey =  @javax.persistence.ForeignKey(name = "none"))
public class UserEntity  {
    @Id
    private String id;
    @NotEmpty
    private String password;
    @Column(table = "a_summary_view",
            name = "b_count")
    private Integer bCount;

}

- monstereo