Hibernate - Limit size of nested collection

Question

i have following model:

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

What i'm struggling to achieve is to fetch all books with pagination, but all of them should have their comments collection limited to 5 latest entries. I came up with following native query (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

This query return correct result set when i run it in console, but when it's runned by the app like this:

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);
}

An syntax error is thrown:

    "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",

I observed that this syntax error is appearing when count query is executed. I also tried providing countQuery attribute (same query but instead of * there is count(*)). This way i get no syntax error, but it returns incorrect result set - book is repeated N times where N is size of comments collection.

How do i fix that? Is there any better approach on this case?

edit:

count query:

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

edit (fetch query):

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=?

Answer 1

This is a perfect use case for Blaze-Persistence Entity Views.

Blaze-Persitence is a query builder on top of JPA which supports many of the advanced DBMS features on top of the JPA model. I created Entity Views on top of it to allow easy mapping between JPA models and custom interface defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure the way you like and map attributes(getters) via JPQL expressions to the entity model. Since the attribute name is used as default mapping, you mostly don't need explicit mappings as 80% of the use cases is to have DTOs that are a subset of the entity model.

A mapping for your model could look as simple as the following

@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();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

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

But the Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/1.4/entity-view/manual/en_US/#spring-data-features

The @Limit annotation was introduced a few weeks ago and will be part of the new release 1.5.0-Alpha2 which will be published in the next few days. You can use 1.5.0-SNAPSHOT in the meantime if you like. The generated SQL roughly looks like this

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

You can also write that query by hand with the query builder which roughly looks like this

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

Answer 2

Simply you can use a @size annotation on the foreign key property to give the upper and lower bound and give the order you want as well.