Specify complex sorting criteria when requesting Spring Data JPA repository

Question

I'm requesting paged and sorted data from my database, specified via Pageable with following repository API:

org.springframework.data.jpa.repository.JpaSpecificationExecutor.findAll(Specification<MyEntity> filter, Pageable pageable)

Pageable contains a Sort with simple sorting criteria, such as sort column X in descending order.

Sometimes, Sort contains one column which actually doesn't exist in the underlying table, which must be replaced by a complex conditional ORDER BY expression:

ORDER BY CASE WHEN entity_to='CUSTOMER' THEN entity_to_id ELSE (CASE WHEN entity_from='CUSTOMER' THEN entity_from_id END) END

Indeed, I can rebuild the Sort instance in Pageable so that this non-existing column gets removed, but I didn't find a way to inject the complex ORDER BY expression in that case.

I tried to find a way, to enrich the resulting query from this invocation of the Repository. I hoped that I can do it by adding another Specification filter, but I got an error (No explicit selection and an implicit one could not be determined), and I'm not sure that the order-by will be applied to the final query built by the Repository implementation:

...
filter = filter.and(
        (root, query, cb) -> {
            final var customerLiteral = cb.literal("CUSTOMER");
            final var caseExpression = cb.selectCase(root.get(MyEntity_.ENTITY_TO))
                    .when(customerLiteral, root.get(MyEntity_.ENTITY_TO_ID)).otherwise(
                            cb.selectCase(root.get(MyEntity_.ENTITY_FROM))
                                    .when(customerLiteral, root.get(MyEntity_.ENTITY_FROM_ID)).otherwise(0));
            final var order = ascElseDesc ? cb.asc(caseExpression) : cb.desc(caseExpression);

            final var cq = cb.createQuery(MyEntity.class);
            return cb.exists(cq.select(root).orderBy(order).subquery(MyEntity.class));
        }
);

repository.findAll(filter, pageable);

I don't want to drop the current implementation which leverages Spring Data's findAll(filter, pageable). I only would like to enrich it in one case. How can it be done?

Answer 1

For that case you can create @Formula column in MyEntity with custom SQL and then refer it in Pageable as regular entity field