Sorting and Cross Join Problem – Spring Data JPA

Affected Version

Spring boot – 1.2.2
Spring data JPA version – 1.7.2
Spring data common – 1.9.2

Problem Statement

When you use custom query using @Query in your spring data JPA repository and pass sort parameter, where sort parameter is a child attribute; the query generated uses cross join instead of left outer join. This gives unexpected result.

Problem Sample


@Query("SELECT e FROM EventApprovalRequest e WHERE e.approvalRequestType = (:approvalRequestType) "
+ "AND e.status = (:status)")
Page getEventsForApproval(
@Param("approvalRequestType") int approvalRequestType, @Param("status") int status, Pageable pageable);

Solution

Pre-define left outer join in your query.

@Query("SELECT e FROM EventApprovalRequest e LEFT OUTER JOIN e.event LEFT OUTER JOIN e.event.director WHERE e.approvalRequestType = (:approvalRequestType) "
+ "AND e.status = (:status)")
Page getEventsForApproval(
@Param("approvalRequestType") int approvalRequestType, @Param("status") int status, Pageable pageable);