[SOLVED] Combining DISTINCT and ORDER BY when ordering by nested property with JPA

Combining DISTINCT and ORDER BY when ordering by nested property with JPA

I have a JPQL query that is looking for distinct records with pageable. There are situations where the pageable sort will be a property in a nested class.

public class Entity1 {
  @Id
  private long id;
  
  @ManyToOne
  private Entity2 entity2;
}

public class Entity2 {
  @Id
  private long id;
  
  private String fieldToSort;
}

public interface Entity1Repository extends JpaRepository<Entity1, Long> {
  Page<Entity1> findDistinct(Pageable pageable);
}

If I call the query sorting by entity2, the correct result is returned. However, when I try and sort by by entity2.fieldToSort, I get the following error:

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

If I remove the DISTINCT restriction, the query runs fine, but returns an Entity1 record for each Entity2.

What is the best way of handling sorting by sub fields, when requiring a distinct record for each of the main entities?

Solution

Solved this by using a nested query in the respository:

@Query("select e1 from Entity1 e1 where e1 in (select e1 from Entity1 e1 left join e1.entity2 e2)")
Page<Entity1> findDistinctCustom(Pageable pageable);