0

TL;DR

The question is:

  • Is there a way to order a query in one table in regards to an attribute of a different table?

Keep reading for more context

In my project I have the following abstract class QueryParams:

public abstract class QueryParams {
    public static final int DEFAULT_PAGE_NUMBER = 0;
    public static final int DEFAULT_PAGE_SIZE = 50;

    private String sort;
    private int page = DEFAULT_PAGE_NUMBER;
    private int size = DEFAULT_PAGE_SIZE;

    public abstract Sort getSorting();
    public abstract BooleanBuilder getPredicate();

    public Pageable getPageable() {
        Sort sorting = getSorting();
        if (Objects.isNull(sorting)) {
            return PageRequest.of(getPage(), getSize());
        }
        return PageRequest.of(getPage(), getSize(), sorting);
    }
}

We extend this class for different entities, so we use the power of QueryDSL to create complex pageable queries. Here's our usage:

entityRepository.findAll(entityQueryParams.getPredicate(), entityQueryParams.getPageable());

The problem arises when I want to sort a query according to a column from a different table. We have a specific entity that holds the ID and TYPE of other entities (no direct relationship) and I'd like to sort the results by the other table's name column.

The Sort object is usually created like so:

@Override
public Sort getSorting() {
    QEntity qEntity = QEntity.qEntity;
    return Sort.by(ASC, qEntity.name.getMetadata().getName());
}
Vítor Antero
  • 106
  • 1
  • 8

1 Answers1

0

Obviously there is, you can join the association in your JPQL query and then sort by any column on that entity.

In QueryDSL one can do:

select(QEntity.entity).from(QEntity.entity)
   .innerJoin(QEntity.entity.association, QAssociation.association)
   .orderBy(QAssociation.association.attribute)

Your custom QueryParams class needs to be extended to support different query roots.

Jan-Willem Gmelig Meyling
  • 3,685
  • 2
  • 21
  • 43