How do I dynamically sort nested entities using Spring Data's Sort?

Question

I have the following JPQL query in a Spring Data Repository:

public interface CarRepository extends Repository<Car, Integer> {
    
    @Query("select distinct c.model from Car c where c.id in :ids")
    Set<Model> findDistinctModelByIdIn(@Param("ids") Set<Integer> ids, Sort sort);
}

A client calls the query as follows (which is exposed via Spring Data REST):

http://localhost:8080/api/cars/search/findDistinctModelByIdIn?ids=1,33,55,43&sort=model.name,desc

However, the results are returned unsorted. How can I sort based on the client sort request parameter?

Does Spring only sort on the domain type the repository manages (e.g., only Car not Model)?

Update

Here is my domain model:

@Entity
@Data
public class Car {
    @Id
    private Long id;
    
    @ManyToOne
    private Model model;
}

@Entity
@Data
public class Model {
    @Id
    private Long id;

    private String name;
}

Update

After turning on trace for org.springframework.web, I found the following:

2023-02-09T12:20:16.315-06:00 TRACE 21812 --- [io-9006-exec-10] o.s.web.method.HandlerMethod : Arguments: [org.springframework.data.rest.webmvc.RootResourceInformation@6e3e0c99, {ids=[33283,37901], sort=[model.name,desc]}, findDistinctModelByIdIn, DefaultedPageable(pageable=Page request [number: 0, size 20, sort: UNSORTED], isDefault=true), UNSORTED, org.springframework.data.rest.webmvc.PersistentEntityResourceAssembler...

However, when using @Yuriy-Tsarkov project, the following is logged:

2023-02-09T12:16:17.818-06:00 TRACE 22460 --- [nio-8097-exec-1] o.s.web.method.HandlerMethod : Arguments: [org.springframework.data.rest.webmvc.RootResourceInformation@3e78567e, {ids=[33283,37901], sort=[model.name,desc]}, findDistinctModelByIdIn, DefaultedPageable(pageable=Page request [number: 0, size 20, sort: model.name: DESC], isDefault=false), model.name: DESC, org.springframework.data.rest.webmvc.PersistentEntityResourceAssembler...

So, Spring is perceiving some difference even though I'm using the exact same version of dependencies and my code & config from what I can tell is the same.

Yuriy Tsarkov · Accepted Answer · 2022-11-03 23:15:49Z

1

Does Spring only sort on the domain type the repository manages (e.g., only Car not Model)?

Actually, does. I've modeled a case like yours and didn't face any problems. Here it is. What if the cornerstone is in the Springs version? It often happens that in some versions the internal implementation is very different...

answered Nov 3, 2022 at 23:15

Yuriy Tsarkov

2,5882 gold badges18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

xerx593 Over a year ago

Nice repo! for http://localhost:8097/pets/search/findDistinctSetByIdIn?ids=1,2,3, i get a result, but issuing ...&sort=name results in 500, ...&sort=toys.name[,desc] produces no error, but has no effect.

Yuriy Tsarkov Over a year ago

@xerx593 Thank you! There is a favoriteToy property in the Pet entity, so the correct sort is sort=favoriteToy.name, desc

xerx593 Over a year ago

..I could reproduce OP issue (with latest spring-boot release), but still cannot find a significant error/difference to your (working) repo.. ;(

James Over a year ago

Thanks for the repo! Unfortunately, my issue remains even after moving to Spring Boot 2.7.5. I have to put the query into the ModelRepository to sort.

James Over a year ago

@yuriy-tsarkov - Your project sorts it correctly. Like xerx593, I can't seem to find a notable difference. We're now using the same Spring Boot version.

|

Ion Ionets · Accepted Answer · 2022-12-07 10:09:13Z

0

Spring supports that for sure, the only thing is that it performs a crossjoin, which means you'll lose the entities with a null Model from the result.

To debug this I would suggest you add this config:

spring:
  jpa:
    show-sql: true

Then you can check in the console/ logs the executed db query and see if the sorting was there, hence find whether the problem is in the query generation or in the data.

answered Dec 7, 2022 at 10:09

Ion Ionets

1131 silver badge9 bronze badges

Comments

James · Accepted Answer · 2023-02-13 15:15:14Z

Summary

Spring Data will not sort on a nested entity that has its own repository and is exported (which is the default).

Details

After further research, I found the answer as to why @yuriy-tsarkov application is able to sort on nested entity but my application is not able.

Cause of the Problem

I have a repository for the nested entity, Model:

public interface ModelRepository extends Repository<Model, Integer> {
    //various method defs    
}

If you add a ToyRepository to @yuriy-tsarkov application, it will fail to sort Toys returned by his PetRepository just like my application fails to sort Models returned by the CarRepository.

Why?

Because once Model (or Toy in the case of yuriy-tsarkov app) has its own repository, it is considered a linkable association. That is, org.springframework.data.rest.webmvc.mapping.Associations.isLinkableAssociation(PersistentProperty<?>) returns true for the model (toy) association. It ultimately returns the boolean from isMapped which is also true :

@Override
public boolean isMapped(PersistentProperty<?> property) {
    return repositories.hasRepositoryFor(property.getActualType()) && super.isMapped(property);
}

This is because repositories.hasRepositoryFor returns true b/c as noted above I have a repository for Model and super.isMapped also returns true. super.isMapped return false only if @RestResource(exported=false) is defined. Since I didn't define that, it return true.

(I believe this may make sense because my Model might be only represented with links. That said, it does seem like I could still may want those links sorted by the model name.)

Solution

In my case I need a repository that manages Model entities. So, I cannot simply remove the ModelRepository.

I can, however, allow the nested entity prop, model, to not be exported:

@Entity
@Data
public class Car {
    @Id
    private Long id;
    
    @ManyToOne
    @RestResource(exported=false)
    private Model model;
}

That allows super.isMapped to return false (because it is not an exported resource). Then sorting does work b/c it's not simply links potentially being returned.

I feel this solution is a workaround though and that Spring Data Rest should still be able to sort on a attribute even if only links are provided. In my scenario, the repo wasn't providing links anyway (even without the @RestResource).

Collectives™ on Stack Overflow

How do I dynamically sort nested entities using Spring Data's Sort?

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related