Order by a field with an underscore in a formula in a Spring Data JPA repository?

I am using a Spring Data JPA repository and have a native query where one of the fields is created using a CASE statement and given an alias with an underscore (e.g., _myfield). This alias is used within the query’s ORDER BY clause. This is how my classes are structured.

public class OuterClass() {
    
    /...
    @Id
    @Column(name = "id")
    private Integer id;


    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "fk_inner", nullable = false, insertable = false, updatable = false)
    private InnerClass innerElement;

}

public class InnerClass() {
    
    /...

    @Formula("""
        CASE
            WHEN t2.field2 IS NULL THEN t2.field3
            ELSE t2.field2 || ' ' || t2.field4
        END) _my_field
    """
    private String _myField;

}

I have tried with different query methods in my JPA Repository for OuterClass, but they all return the same kind of error when trying to generate the ORDER BY query. Some of my tries include, but are not limited to, these kind of methods:

@EntityGraph(attributePaths = {"innerElement"})
@Query("SELECT o FROM OuterClass o WHERE o.id = :id ORDER BY o.innerElement._myField")
List<OuterElement> findById(@Param("id") Integer id);

@EntityGraph(attributePaths = {"innerElement"})
List<OuterElement> findByIdOrderByInnerElement__MyField(@Param("id") Integer id);

These all generate the same error because JPA tries to generate the ORDER BY clause by including the CASE statement

SELECT
  ...
WHERE 
  ...
ORDER BY 
  CASE
        WHEN t2.field2 IS NULL THEN t2.field3
        ELSE t2.field2 || ' ' || t2.field4
  END) _my_field

Which always generates a SQL Exception.

How can I make this query work properly while keeping the alias as _myField in the ORDER BY clause?

You need to sign in to view this answers

Related Post