Spring Data Rest - sort by nested property

Question

I have a database service using Spring Boot 1.5.1 and Spring Data Rest. I am storing my entities in a MySQL database, and accessing them over REST using Spring's PagingAndSortingRepository. I found this which states that sorting by nested parameters is supported, but I cannot find a way to sort by nested fields.

I have these classes:

@Entity(name = "Person")
@Table(name = "PERSON")
public class Person {
    @ManyToOne
    protected Address address;

    @ManyToOne(targetEntity = Name.class, cascade = {
        CascadeType.ALL
    })
    @JoinColumn(name = "NAME_PERSON_ID")
    protected Name name;

    @Id
    protected Long id;

    // Setter, getters, etc.
}

@Entity(name = "Name")
@Table(name = "NAME")
public class Name{

    protected String firstName;

    protected String lastName;

    @Id
    protected Long id;

    // Setter, getters, etc.
}

For example, when using the method:

Page<Person> findByAddress_Id(@Param("id") String id, Pageable pageable);

And calling the URI http://localhost:8080/people/search/findByAddress_Id?id=1&sort=name_lastName,desc, the sort parameter is completely ignored by Spring.

The parameters sort=name.lastName and sort=nameLastName did not work either.

Am I forming the Rest request wrong, or missing some configuration?

Thank you!

name.lastName would be the property to use. Ordering by nested properties works fine for me in the Hopper release but I did experience the following bug in an RC version of the Ingalls release. This is reported as being fixed however I have not tried it. jira.spring.io/browse/… — Alan Hay
– Alan Hay, Commented Feb 16, 2017 at 10:50
@AlanHay You are the Man, working with me after downgrade to Hopper release <spring.data.jpa.version>1.10.10.RELEASE</spring.data.jpa.version <spring.data.rest.webmvc.version>2.5.10.RELEASE</spring.data.rest.webmvc.version> — Khaled Lela
– Khaled Lela, Commented May 13, 2017 at 4:37
@AlanHay BTW, I tried v3.0.0.M3 that reported that fixed but not working with me. — Khaled Lela
– Khaled Lela, Commented May 13, 2017 at 4:53
Was someone able to solve the problem? It doesn't work with SDR 3.0.2.RELEASE and Spring Boot 1.5.8. I'm using sort=property_subproperty. Thanks — drenda
– drenda, Commented Dec 14, 2017 at 11:29
We were facing the same problem (using Spring Boot 1.5.9 with Spring Data REST 2.6.9). The nested property that we tried to use for sorting was covered with a Jackson Mixin containing @JsonProperty(access = READ_ONLY). Removing this annotation lead to proper sorting behaviour for this nested property. — jensfischerhh
– jensfischerhh, Commented Mar 14, 2018 at 12:48

lrv · Accepted Answer · 2017-12-20 21:00:16Z

The workaround I found is to create an extra read-only property for sorting purposes only. Building on the example above:

@Entity(name = "Person")
@Table(name = "PERSON")
public class Person {

    // read only, for sorting purposes only
    // @JsonIgnore // we can hide it from the clients, if needed
    @RestResource(exported=false) // read only so we can map 2 fields to the same database column
    @ManyToOne
    @JoinColumn(name = "address_id", insertable = false, updatable = false) 
    private Address address;

    // We still want the linkable association created to work as before so we manually override the relation and path
    @RestResource(exported=true, rel="address", path="address")
    @ManyToOne
    private Address addressLink;

    ...
}

The drawback for the proposed workaround is that we now have to explicitly duplicate all the properties for which we want to support nested sorting.

LATER EDIT: another drawback is that we cannot hide the embedded property from the clients. In my original answer, I was suggesting we can add @JsonIgnore, but apparently that breaks the sort.

miran · Accepted Answer · 2017-12-18 19:15:37Z

I debugged through that and it looks like the issue that Alan mentioned.

I found workaround that could help:

Create own controller, inject your repo and optionally projection factory (if you need projections). Implement get method to delegate call to your repository

 @RestController
 @RequestMapping("/people")
 public class PeopleController {

    @Autowired
    PersonRepository repository;

    //@Autowired
    //PagedResourcesAssembler<MyDTO> resourceAssembler;

    @GetMapping("/by-address/{addressId}")
    public Page<Person> getByAddress(@PathVariable("addressId") Long addressId, Pageable page)  {

        // spring doesn't spoil your sort here ... 
        Page<Person> page = repository.findByAddress_Id(addressId, page)

        // optionally, apply projection
        //  to return DTO/specifically loaded Entity objects ...
        //  return type would be then PagedResources<Resource<MyDTO>>
        // return resourceAssembler.toResource(page.map(...))

        return page;
    }

}

This works for me with 2.6.8.RELEASE; the issue seems to be in all versions.

pdorgambide · Accepted Answer · 2020-05-05 18:52:57Z

From Spring Data REST documentation:

Sorting by linkable associations (that is, links to top-level resources) is not supported.

https://docs.spring.io/spring-data/rest/docs/current/reference/html/#paging-and-sorting.sorting

An alternative that I found was use @ResResource(exported=false). This is not valid (expecially for legacy Spring Data REST projects) because avoid that the resource/entity will be loaded HTTP links:

JacksonBinder
BeanDeserializerBuilder updateBuilder throws
 com.fasterxml.jackson.databind.exc.MismatchedInputException: Cannot construct instance of ' com...' no String-argument constructor/factory method to deserialize from String value

I tried activate sort by linkable associations with help of annotations but without success because we need always need override the mappPropertyPath method of JacksonMappingAwareSortTranslator.SortTranslator detect the annotation:

            if (associations.isLinkableAssociation(persistentProperty)) {
                if(!persistentProperty.isAnnotationPresent(SortByLinkableAssociation.class)) {
                    return Collections.emptyList();
                }
            }

Annotation

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface SortByLinkableAssociation {
}

At project mark association as @SortByLinkableAssociation:

@ManyToOne
@SortByLinkableAssociation
private Name name;

Really I didn't find a clear and success solution to this issue but decide to expose it to let think about it or even Spring team take in consideration to include at nexts releases.

Andriy · Accepted Answer · 2021-02-11 14:55:05Z

0

Please see https://stackoverflow.com/a/66135148/6673169 for possible workaround/hack, when we wanted sorting by linked entity.

answered Feb 11, 2021 at 14:55

Andriy

2693 silver badges8 bronze badges

Collectives™ on Stack Overflow

Spring Data Rest - sort by nested property

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related