'JPA CRITERIA QUERY with order by joined columns

How to invoke order by on a joined entity? I am trying to achieve the following with:

select * from person p inner join telephone t on p.id=t.person_id join sim s on s.id=t.sim_id order by s.name DESC

@Entity
public class Person implements Serializable{
    @Id
    private Long id;
    @OneToMany(orphanRemoval = true, mappedBy = "person", fetch = FetchType.LAZY, cascade = CascadeType.PERSIST)
    private List<Telephone> telephonesNumber;

@Entity
public class Telephone implements Serializable {
    @Id
    private String number;
    @Id
    @ManyToOne()
    @JoinColumn(name = "person_id")
    private Person person;
    @Id
    @ManyToOne(cascade = {})
    @JoinColumn(name = "sim_id")
    private Sim sim;

@Entity
public class Sim implements Serializable {
    @Id
    private Long id;
    @Column(unique = true)
    private String name;

I use specification interface, in this example sorting is on the field person.id and it works

public class PersonSpecification implements Specification<Person> {

    @Override
    public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
         List<Predicate> predicates = new ArrayList<>();
// there is many different conditions for example
//       if(someCondition!=null) {
//          predicates.add(builder.like(root.get("someProperty"), someValue));
//      }


        query.groupBy(root.get("id"));
        //there I want to order by Sim.name i dont know how
        query.orderBy(builder.asc(root.get("phone")));//this works
        return  builder.and((predicates.toArray(new Predicate[predicates.size()])));
    }

I want to order by Sim.name but i dont know how.



Solution 1:[1]

In JPA specification you can use:

query.orderBy(builder.asc(root.join("telephonesNumber").get("sim").get("name")));

to sort by sim name.

For more details: https://en.wikibooks.org/wiki/Java_Persistence/Querying#Joining.2C_querying_on_a_OneToMany_relationship

If you using JPA Query:

@Query("select s from Person p 
        join p.telephonesNumber t 
        join t.sim s order 
        by t.sim.id desc")

It will produce this:

select * from person p 
inner join telephone t on p.id=t.person_id 
inner join sim s on t.sim_id=s.id 
order by t.sim_id desc

For more details:

https://github.com/abhilekhsingh041992/spring-boot-samples/blob/master/jpa/src/main/java/example/springboot/jpa/repository/PersonRepository.java

Solution 2:[2]

another way for that would be using Query method:

  List<Telephone> findAllByOrderBySimIdAsc();

Look at this findAllByOrderBySimIdAsc

With the code before, you can get all rows from Telephone ordered by Sim Id.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 ceduard0