Spring-Data FETCH JOIN with Paging is not working

Question

I am trying to use HQL fetching my entity along with sub-entities using JOIN FETCH, this is working fine if I want all the results but it is not the case if I want a Page My entity is ``` @Entity @Data public class VisitEntity { @Id @Audited private long id; . . . @OneToMany(cascade = CascadeType.ALL,) private List comments; } ``` and because I have millions of visits I need to use Pageable and I want to Fetch the comments in a single database query like : ``` @Query("SELECT v FROM VisitEntity v LEFT JOIN FETCH v.comments WHERE v.venue.id = :venueId and ..." ) public Page getVenueVisits(@Param("venueId") long venueId,..., Pageable pageable); ``` That HQL call throws the following exception: ``` Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properti…

Accepted Answer

The easiest way is to use the `countQuery` attribute of the the `@Query` annotation to provide a custom query to be used.

```
@Query(value = "SELECT v FROM VisitEntity v LEFT JOIN FETCH v.comments …",
       countQuery = "select count(v) from VisitEntity v where …")
List<VisitEntity> getVenueVisits(@Param("venueId") long venueId, …);
```

Spring-Data FETCH JOIN with Paging is not working

Solution

Problem

Related problems