Spring JPA PageRequest ordering by a join column

Question

I'm using the Spring PageRequest to sort (order) a custom query by a column in my database.

If I'm doing a custom query such as :

 @Query( value = "select h from hunterhouse h join h.queens q where q.name = 'Computer Science'") 

Is it not possible to sort by a column in q, the table I am joining to?

PageRequest request = new PageRequest(page, size, Sort.Direction.DESC, "q.region");

debug comes out as "order by h.q.region" which is incorrect, is it not possible to order by a join column?

Answer 1

I had the same problem! This bug was fixed in Spring Data JPA 1.7.3 - just make sure you have a version higher or equal to 1.7.3! After that it will work with queens.region

Spring source: https://jira.spring.io/browse/DATAJPA-726

Answer 2

You simply put the complete path you want to join on into the sort expression. So what you'd need to use is queens.region which will then be translated into h.queens.region and appended to the JPQL query you defined.