CODEWITHSUNDEEP
springspring-bootspring-data-jpaspring-dataspring-data-rest
James
James

Reputation: 3184

Which repository for sorting on a nested entity property using Spring Data JPA?

I want to get sorted list of Models for a given set of Car vins. The following repository query attempts to do this:

@Query("select distinct c.model"
        + "from Car c "
        + "where c.id in :vins")
public Set<Model> findDistinctModelForCarIdsIn(@Param("vins") Set<Integer> vins, Sort sort);

But it appears sort does not work if I put this in a ModelRepository:

http://localhost/api/models/search/findDistinctModelForCarIdsIn?vins=16203,25617,42661&sort=model.name,asc

If instead I put findDistinctModelForCarIdsIn in CarRepository, the sort works:

http://localhost/api/cars/search/findDistinctModelForCarIdsIn?vins=16203,25617,42661&sort=model.name,asc

Why is this? Is the rule to put the method in the repository corresponding to the entity that I selecting from (e.g., Car) instead of the what I'm selecting (e.g., Model)?

Here are my domain objects:

class Car {
  Integer id;

  @ManyToOne 
  Model model;
}

class Model {   
   Integer id;
   String name;
}

Upvotes: 0

Views: 86

Answers (1)

Max Kozlov
Max Kozlov

Reputation: 36

I hope I understand the essence of the question, so I will try to give an answer.

As far as I know, for this type of query, sorting is applied as follows:

select distinct c.model
from Car c 
where c.id in :vins
order by c.{prod} {desc|asc}

An alias is taken from the from section and sorting is applied to it. In your case it is a from Car c. Alias is c.

In any case, I would advise you to enable the jpa debug mode using the following settings and look at the sql requests themselves

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Upvotes: 1

Related Questions