Spring Data JPA: Sorting and paging with joined tables

Question

I have a scenario where I want to filter, sort and page over a result where 3 tables take part.

At the moment I use Spring Data JPA's Specification feature to do it on a single entity: repository.findAll(specification, pageRequest).

This works great, but now I have another scenario where the sort / filter attributes are spread over 3 tables which are connected by one-to-many relations.

Here is my scenario:

@Entity
public class CustomerEntity ... {
  ...

  @Column(nullable = false)
  public String                                 customerNumber;

  @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL, orphanRemoval = true)
  public List<CustomerItemEntity> items;
}


@Entity
public class CustomerItemEntity ... {
  ...

  @Column(nullable = false)
  public String                                 itemNumber;

  @ManyToOne(optional = false)
  @JoinColumn(name = "customerId")
  public CustomerEntity customer;

  @OneToMany(mappedBy = "item", cascade = CascadeType.ALL, orphanRemoval = true)
  public List<DocumentEntity> documents;
}


@Entity
public class DocumentEntity ... {
  ...

  @Column(nullable = false)
  public LocalDate                                 validDate;

  @ManyToOne(optional = false)
  @JoinColumn(name = "itemId")
  public CustomerItemEntity item;
}

Is there a way to use PageRequest and Specification where customerNumber, itemNumber and validDate are used for filtering, sorting and paging at the same time?

Answer 1

Try something like this:

Specification<CustomerEntity> joins = (customer, query, cb) ->  {
    // from CustomerEntity c
    // join c.items i
    Join<CustomerEntity, CustomerItemEntity> items = customer.join("items");

    // join i.documents d
    Join<CustomerItemEntity, DocumentEntity> documents = items.join("documents");

    // // where c.customerNumber = ?1 and i.itemNumber = ?2 and d.validDate = ?3 
    return cb.and( 
            customer.equal(customer.get("customerNumber", customerNumber)),
            items.equal(items.get("itemNumber", itemNumber)), 
            documents.equal(documents.get("validDate", validDate))
    );
};

// sort by c.customerNumber asc
PageRequest pageRequest = new PageRequest(0, 2, new Sort(Sort.Direction.ASC, "customerNumber"));

Page<CustomerEntity> customerPage = CustomerRepo.findAll(joins, pageRequest);

But I don't know why do you need Specification here?

You can make the same more simpler:

@Query("select c from CustomerEntity c join c.items i join i.documents d where c.customerNumber = ?1 and i.itemNumber = ?2 and d.validDate = ?3")
Page<CustomerEntity> getCustomers(String customerNumber, String itemNumber, LocaleDate validDate, Pageable pageable);  

But all this does not make sense since your three entities have sequential one-to-many associations. In this case instead of three conditions you can use only last one: where d.validDate = ?1. Then a query method became even easier:

@Query("select c from CustomerEntity c join c.items i join i.documents d where d.validDate = ?1")
Page<CustomerEntity> getCustomers(LocaleDate validDate, Pageable pageable);

UPDATE

To add sorting by a field of joined entity we can use orderBy method of the query:

Specification<CustomerEntity> joins = (customer, query, cb) ->  {

    Join<CustomerEntity, CustomerItemEntity> items = customer.join("items");
    Join<CustomerItemEntity, DocumentEntity> documents = items.join("documents");

    // Ascending order by 'Document.itemNumber'
    query.orderBy(cb.asc(documents.get("itemNumber")));

    return cb.and( 
            customer.equal(customer.get("customerNumber", customerNumber)),
            items.equal(items.get("itemNumber", itemNumber)), 
            documents.equal(documents.get("validDate", validDate))
    );
};

Page<CustomerEntity> customerPage = CustomerRepo.findAll(joins, new PageRequest(0, 2));

To sort by several parameters you can pass them to the method separated by commas or by a List:

query.orderBy(cb.asc(items.get("customerNumber")), cb.desc(documents.get("itemNumber")));

Answer 2

I found out that you can sort by contents of OneToMany column by using join alias you can create using custom query. In repository my code it was:

@Query("select p from Person p join p.roles r")
Page<Person> search(Pageable pageable);

So I was able to sort using:

r.role

Passed from controller using:

@PageableDefault(size = 15, sort = "r.role") Pageable pageable