Hibernate - 限制嵌套集合的大小

我有以下型号：

@Entity
@Data
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String author;
    private String title;
    @OneToMany(cascade = CascadeType.MERGE)
    private List<Comment> comments;
}

@Entity
@Data
public class Comment {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String body;
    private LocalDateTime timestamp;
}

我正在努力实现的是获取所有带分页的书籍，但所有这些书籍的评论集合都应限制为 5 个最新条目。我想出了以下本机查询（mssql）：

select *
from book b
         left join book_comments bc on bc.book_id = b.id and bc.comments_id in (
    select top 5 c2.id
    from comment c2
             join book_comments bc2 on c2.id = bc2.comments_id
             join book b2 on bc2.book_id = b2.id
    where bc2.book_id = b.id
    order by c2.timestamp desc
)
         left join comment c
                   on bc.comments_id = c.id

当我在控制台中运行该查询时，该查询返回正确的结果集，但当它由应用程序运行时，如下所示：

public interface BookRepository extends JpaRepository<Book, Long> {
    @Query(value = "select * " +
            "from book b " +
            "         left join book_comments bc on bc.book_id = b.id and bc.comments_id in ( " +
            "    select top 5 c2.id " +
            "    from comment c2 " +
            "             join book_comments bc2 on c2.id = bc2.comments_id " +
            "             join book b2 on bc2.book_id = b2.id " +
            "    where bc2.book_id = b.id " +
            "    order by c2.timestamp desc " +
            ") " +
            "         left join comment c " +
            "                   on bc.comments_id = c.id",
            nativeQuery = true)
    Page<Book> findAll(Pageable pageable);
}

抛出语法错误：

    "localizedMessage": "Incorrect syntax near 'id'.",
    "message": "Incorrect syntax near 'id'.",
    "suppressed": []
},
"localizedMessage": "could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet",

我观察到执行计数查询时出现此语法错误。我还尝试提供 countQuery 属性（相同的查询，但不是 *，而是 count(*)）。这样我就不会出现语法错误，但它返回不正确的结果集 - 书被重复 N 次，其中 N 是评论集合的大小。

我该如何解决这个问题？对于这个案例，有没有更好的处理方法呢？

edit:

计数查询：

select count(*)
from book b
         left join book_comments bc on bc.book_id = b.id and bc.comments_id in (
    select top 5 c2.id
    from comment c2
             join book_comments bc2 on c2.id = bc2.comments_id
             join book b2 on bc2.book_id = b2.id
    where bc2.book_id = b.id
    order by c2.timestamp desc
)
         left join comment c
                   on bc.comments_id = c.id

编辑（获取查询）：

select comments0_.book_id as book_id1_1_0_, comments0_.comments_id as comments2_1_0_, comment1_.id as id1_2_1_, comment1_.body as body2_2_1_, comment1_.timestamp as timestam3_2_1_ from book_comments comments0_ inner join comment comment1_ on comments0_.comments_id=comment1_.id where comments0_.book_id=?

这是一个完美的用例Blaze-持久性实体视图 https://github.com/Blazebit/blaze-persistence#entity-view-usage.

Blaze-Persitence 是基于 JPA 的查询生成器，它支持基于 JPA 模型的许多高级 DBMS 功能。我在其之上创建了实体视图，以允许在 JPA 模型和自定义接口定义的模型之间轻松映射，就像类固醇上的 Spring Data Projections 一样。这个想法是，您按照自己喜欢的方式定义目标结构，并通过 JPQL 表达式将属性（getter）映射到实体模型。由于属性名称用作默认映射，因此您通常不需要显式映射，因为 80% 的用例都具有作为实体模型子集的 DTO。

您的模型的映射可能看起来像下面一样简单

@EntityView(Book.class)
interface BookDto {
  @IdMapping
  Long getId();
  String getAuthor();
  String getTitle();
  @Limit(limit = 5, order = { "timestamp DESC", "id DESC"})
  List<CommentDto> getComments();
}
@EntityView(Comment.class)
interface CommentDto {
  @IdMapping
  Long getId();
  String getBody();
}

查询是将实体视图应用于查询，最简单的就是通过 id 进行查询。

BookDto dto = entityViewManager.find(entityManager, BookDto.class, id);

但是 Spring Data 集成允许您像 Spring Data Projections 一样使用它：https://persistence.blazebit.com/documentation/1.4/entity-view/manual/en_US/#spring-data-features https://persistence.blazebit.com/documentation/1.4/entity-view/manual/en_US/#spring-data-features

The @Limit注释是在几周前引入的，并将成为新版本 1.5.0-Alpha2 的一部分，该版本将在未来几天内发布。如果您愿意，可以同时使用 1.5.0-SNAPSHOT。 生成的SQL大致是这样的

select b.id, b.author, b.title, c.id, c.body
from book b
cross apply (
  select c.id, c.body, c.timestamp
  from book_comments bc 
  join comment c on bc.comments_id = c.id
  where bc.book_id = b.id
  order by timestamp desc, id desc
  limit 5
) c

您还可以使用查询生成器手动编写该查询，大致如下所示

criteriaBuilderFactory.create(entityManager, Tuple.class)
  .from(Book.class, "b")
  .leftJoinLateralEntitySubquery("b.comments", "c", "subC")
    .orderByDesc("subC.timestamp")
    .orderByDesc("subC.id")
    .setMaxResults(5)
  .end()
  .getResultList();