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
Leave feedback about this