I have an one to many relashionship between Produto and Variacao and a many to one between Produto and Franquia.

I'd like to paginate the Produto entity with its children loaded at once, I wouldn't like to paginate the children for a given Produto;

The method below (all entities are FetchType.LAZY):

@Query("SELECT p FROM Produto p " +
        "JOIN FETCH p.variacoes v " +
        "JOIN FETCH p.franquia f " +
        "WHERE f = :franquia ")
Page<Produto> findAllByFranquia(PageRequest pageRequest,Franquia franquia);

generates the following issue

WARN: HHH90003004: firstResult/maxResults specified with collection fetch;
                   applying in memory

The only solution for PageRequest with JOIN FETCH (not just limiting the results) I know is to use two separeted queries:

One to page the ids:

@Query("SELECT p.systemId " +
        "FROM Produto p " +
        "WHERE p.franquia = :franquia")
Page<Long> pagedIds(PageRequest pageRequest,Franquia franquia);

And another one to fetch entities:

@Query("SELECT p FROM Produto p " +
        "JOIN FETCH p.variacoes v " +
        "JOIN FETCH p.franquia f " +
        "WHERE p.systemId IN :ids ")
List<Produto> findAllByIdIn(List<Long> ids);

Having too many ids is a perfomance problem and I truly wouldn't like to set batch logic to just paginate my entity.

I also tried to remove JOIN FETCH and just initialize the collection with Hibernate.initialize on page.getContent(), but it initializes nothing as the content is an array list not an entity then I would loop over those entities falling on N+1 queries.

Setting FetchType.EAGER will also not solve the problem as it also has N+1 queries on findAllByFranquia call (yes, EAGER approach will query each row from parent entity to get the child one even out of a for/stream loop ).