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!

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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