Sort by joined table's field Spring JPA

Question

I have two entity classes Request,User:

//Ommiting some annotations for brevity
 public class User{    
   private Long id;
   private String name;
   private Integer age;
}

public class Request{
  private Long id;
  private String message;
  private Date createTime;

  @ManyToOne
  @JoinColumn(name="user_id")
  private User user;
}

I can sort request list by create time :

Sort = new Sort(Direction.ASC,"createTime");

Is there a possible way to sort request list by User's name? Like:

Sort = new Sort(Direction.ASC,"User.name");

Answer 1

Yes. new Sort(Direction.ASC,"user.name"); should work just fine.

Spring Data JPA will left outer join the User to the Request and order by the joined column's name resulting in SQL like this:

select 
  id, message, createTime 
from 
  Request r 
  left outer join User u on u.id = r.user_id 
order by 
  u.name asc

This works great on one-to-one and, like you have here, many-to-one relationships but because a left outer join is employed to implement the order by clause, if the joined entity represents a many relationship (like in a one-to-many), the Sort may result in SQL in which duplicate records are returned. This is the case because the Sort parameter will always result in a new left outer join even if the entity being joined is already joined in the query!

Edit Incidentally, there is an open ticket concerning this issue: https://jira.spring.io/browse/DATAJPA-776