Spring Data paginating and sorting on calculated fields

Question

We use spring data to display a list of calculated fields in a paginated and sortable table :

@Query(value = 
    "select new com.mycompany.SomeDTO( " +
    "    c.id as creditAdviceId, " +
    "    case when c.associationAmount.assignedAmountNet = 0 "+
    "        then c.netReceivedAmount  "+
    "        else 0 "+
    "        end "+
    "        as toAllocateAmount, " +
    "    c.netReceivedAmount - c.associationAmount.assignedAmountNet "+
    "        as notAllocatedAmount",
    ") " +
    "from CreditAdvice c where c.debtorCompany.id = :companyId",

    countQuery = "select count(c.id) from CreditAdvice c where c.debtorCompany.id = :companyId")

Page<SomeDTO> creditAdviceMonitoring(@Param("companyId") long companyId, Pageable pageable);

Everything works fine like this except the sorting support.

To sort on a calculated field, Spring Data (or JPA ?) automatically appends this statement :

... order by c.toAllocateAmount desc

which is invalid because c.toAllocateAmount doesn't exist on CreditAdvice entity.

But the same request tested in a JPA Console works fine (because of the alias in the select statement) :

... order by toAllocateAmount desc

The question is : is there a way or a workaround, to tell spring data to generate a custom order by clause. Some king of mapping to tell him the code to generate depending on the required sorting field

Answer 1

Short answer : encapsulate sorted field from original pageable object with parenthesis, like this :

public static Pageable parenthesisEncapsulation(Pageable pageable) {

    List<Sort.Order> orders = new ArrayList<>() ;
    for (Sort.Order order : pageable.getSort()) {
        String encapsulatedProperty = "("+order.getProperty()+")" ;
        orders.add( new Sort.Order(order.getDirection(), encapsulatedProperty));
    }

    return new PageRequest(pageable.getPageNumber(), pageable.getPageSize(), new Sort(orders)) ;
}

To understand why, take a look at how Spring Data generates "order by" clause when using paginated requests : QueryUtils.getOrderClause()

This sounds more like a hack than a real solution... but it works fine.

Optionnaly, if you want to use the resulting Pageable object from the resulting query (Page<T>), you may have to remove the previously added parenthesis. (Use case : displaying sorted columns in datatable headers)