Sorting entities by their @OneToMany relationship property with Spring Data JPA

Question

I'm working on Spring Boot web application which uses Spring Data JPA for its persistance layer. When retrieving entities from repository I'm using Spring's Data JPA Sort object to sort them. It works when I'm sorting by retrieved entity property or by it's @OneToOne relationship object property, but I would like to use it to sort by one of the @OneToMany relationship object properties.

Let's explain with an example: suppose I have entity object Author which has one to many relationship with another entity Book. My entity classes in simplest form would look like this:

@Entity
public class Author {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @OneToMany(mappedBy = "author")
    private List<Book> books;

    <constructor, getters, setters etc.>
}

and

@Entity
public class Book {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String title;

    @ManyToOne
    @JoinColumn(name = "author_id")
    private Author author;

    <constructor, getters, setters etc.>
}

now, when I'm retrieving authors with Spring's repository interface I'm passing Sort object to it like this one:

new Sort(Sort.Direction.ASC, "id")

which gives me results sorted by author id ascending. I would like to pass something like this:

new Sort(Sort.Direction.ASC, "books.title")

Let's say I have this data in the database (simplified table just to show example):

author  | book_title
---------------------
Andrew  | Letter C
Barbara | Letter A
Andrew  | Letter B
Barbara | Letter D

The resulting list would be Barbara (her book "Letter A" is first after sorting by book title) then Andrew.

Passing new Sort(Sort.Direction.ASC, "books.title") right now results in "Barbara, Andrew, Andrew, Barbara" - which means there are duplicates on resulting list - I would like results to be distinct.

I do not want to use @OrderBy on collection in Author as I'm not interested in actual books order - only the authors.

I do not want to sort results with JPQL on repository level with @Query (it would be probably possible with some JPQL subquery and virtual field maybe), as I need it to be able to accept sortable fileds dynamically (it may be title now, but isbn number on other case and my API should be able to take one or the other).

It has to work with Spring Specification API which I'm using to filter results.

Is it possible?

Answer 1

I had the same question, but I found this answer:

@OneToMany
@OrderBy("value ASC") // sort by value ASC
private List<PropertyDefinition> propertyDefinitions;

Check the answer in this link: Spring Data JPA sorting on nested collection

It solve my issue.