Paging with Fetch Joins

If you combine a collection fetch join with paging, the SQL cannot use LIMIT (each order produces several rows, one per item). Hibernate then loads all matching rows and pages in memory, and warns you:

@Query("select o from Order o left join fetch o.items where o.user.id = :userId")
Page<Order> findWithItems(@Param("userId") Long userId, Pageable pageable);

WARN org.hibernate.orm.query : HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory

For a user with 5,000 orders this loads all 5,000 orders and their items to return 20. Make Hibernate fail fast instead: spring.jpa.properties.hibernate.query.fail_on_pagination_over_collection_fetch=true.

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("select o.id from Order o where o.user.id = :userId")
    Page<Long> findIdsByUserId(@Param("userId") Long userId, Pageable pageable);   // LIMIT works

    @Query("select o from Order o left join fetch o.items where o.id in :ids")
    List<Order> findWithItemsByIdIn(@Param("ids") List<Long> ids);
}

@Transactional(readOnly = true)
public Page<OrderDto> myOrders(Long userId, Pageable pageable) {
    Page<Long> ids = orders.findIdsByUserId(userId, pageable);
    Map<Long, Order> byId = orders.findWithItemsByIdIn(ids.getContent()).stream()
            .collect(Collectors.toMap(Order::getId, o -> o));
    return ids.map(id -> OrderDto.from(byId.get(id)));      // keep the sorted order of the id page
}

query 1: select o.id ... order by ... limit 20      -> [512, 509, 507, ...]   (paged in the DB)
 query 2: select count(o.id) ...                     -> 5000                   (totalElements)
 query 3: select o, i ... where o.id in (512, ...)   -> 20 orders + items      (one fetch join)