JpaSort.unsafe throws exception for non-entity properties [DATAJPA-1643]

[spring-projects-issues]

Milan Milanov opened DATAJPA-1643 and commented

I'm using the spring boot starter JPA version 2.2.1 RELEASE, which pulls the spring data JPA project with the same dependency. I'd like to use the specification + paging findAll method, but with a twist, that the sorting should be done based on a function, and not on a simple property.

Sort sort = JpaSort.unsafe("nlssort(entity_title, 'NLS_SORT = XGERMAN_DIN_AI')");

var activities = entityRepository.findAll(new EntitySpecification(...), PageRequest.of(0, 10, sort))

Running this code I get an exception saying that No property nlssort(entity found for type Entity!. The same question is asked here, and in fact the accepted answer actually works. The problem is that adding a query doesn't play along with a specification. Just for test i tried:

@Query("SELECT s FROM Entity s")
Page<Entity> findAll(Specification<Entity> spec, Pageable pageable);

Then the query is actually generated and executed, but all results are just sorted, nothing is filtered. Also i don't know how this would work since there are aliases, but nevertheless:

SELECT * 
FROM   (SELECT ...
               entity0_.entity_title        AS entity_title6_1_, 
               ...
        FROM   entities entity0_ 
        ORDER  BY nlssort(entity_title, 'NLS_SORT = XGERMAN_DIN_AI') ASC) 
WHERE  ROWNUM <= ? 

---

Affects: 2.2.3 (Moore SR3)

[incepter]

Hello,

Did you find a workaround for this ? Im also stick with this:

org.springframework.data.mapping.PropertyReferenceException: No property (CASE WHEN consition THEN value END) found for type BatchCollectOperationItem!

Thanks,

[incepter]

We ended up doing something similar to this:

public static Specification<Example> getOrderSpecification() {
        return (root, cq, cb) -> {
            final Expression<Object> orderCase = cb.selectCase(root.get(Example_.PROCESSING_STATUS))
                    .when(ExampleStatus.PENDING.name(), 0)
                    .when(ExampleStatus.ERROR.name(), 1)
                    // ...
                    .otherwise(99);
            final Order order = cb.asc(orderCase);
            cq.orderBy(order);
            return null;
        };
    }

[xiaohulu]

2.7.5 also have this bug

[brandonfl]

Hello,

The problem is still present in version 2.7.6
Any idea on how to disable the verification or something similar ?

I dont find any workaround, the solution from @incepter dont work when we use it with PostgreSQL DISTINCT

Thanks in advance,
Regards,
BrandonFL

[mp911de]

When using Specifications, order-by must be an expression and be translated into such. This will be addressed via #3172 so closing this one as duplicate.