0

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;
}
James
  • 2,876
  • 18
  • 72
  • 116

1 Answers1

1

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
Max Kozlov
  • 36
  • 3
  • Thanks for your answer. I've enabled the SQL logging and can see that an `ORDER BY` clause is missing when that `@Query` is in a `ModelRepository`. I do see the `ORDER BY` clause when the `@Query` is in a `CarRepository`. My question is why does it not work in the `ModelRepository`. If you can answer that question, I will accept and upvote. Thanks. – James Jul 07 '23 at 16:57
  • @James after a short debug, I found out that it doesn’t matter in which repository to write this request. But there is the following behavior, for my version of **spring boot 2.7.7**, the `select distinct` expectedly causes an `ERROR error: for SELECT DISTINCT, ORDER BY expressions must appear in select list` for the query from the example. Therefore, I can only assume that in your case the `order by` is not added to the request, just to avoid this error. – Max Kozlov Jul 08 '23 at 11:36