jakartaee / jakartaee/persistence
Support for custom expressions in @OrderBy annotation and ORDER BY clause
- Dominant language
- Java
- Stars
- 268
- Forks
- 78
- Avg merge
- 1d 6h
- Merged PRs (30d)
- 13
Description
Currently, the JPA spec only allows column names or paths (embeddables only) in the `ORDER BY` clause in JPQL expressions, but also for the `@OrderBy` annotation or XML element (more or less).
From the JPA 2.2 spec PDF:


It's often worthwhile (or even required) to get an __expression__ into the `ORDER BY` clause to do the sorting. It's also much more natural to allow custom (JPQL) expressions to match what SQL is able to accomplish.
__Examples:__
https://stackoverflow.com/questions/59587201/jpa-hibernate-case-in-orderby-annotation-throws-exception-unexpected-token-ca (JPA annotation, not working)
https://discourse.hibernate.org/t/mapping-orderby-with-case-when-fails-with-npe-unexpected-token-case/3589 (JPA + Hibernate annotations, not working)
https://stackoverflow.com/questions/19300911/case-when-in-hibernate-orderby-annotation (Hibernate annotation, not working)
https://stackoverflow.com/questions/9907871/order-by-condition-hql (Hibernate HQL, not working)
https://stackoverflow.com/questions/30903801/hql-order-by-expression (Hibernate HQL, not working)
The only way to achieve `ORDER BY` by some expression seems to be to add that expression to the `SELECT ` along with an `AS result_variable`, see JPQL BNF:
```
...
select_clause ::= SELECT [DISTINCT] select_item {, select_item}*
select_item ::= select_expression [[AS] result_variable]
select_expression ::=
single_valued_path_expression |
scalar_expression |
aggregate_expression |
identification_variable |
OBJECT(identification_variable) |
constructor_expression
...
orderby_clause ::= ORDER BY orderby_item {, orderby_item}*
orderby_item ::=
state_field_path_expression | general_identification_variable | result_variable
[ ASC | DESC ]
```
But what about the `@OrderBy` annotation?
Seems to be an issue for quite some people out there.
Please extend.
Contributor guide
Research direction
Start with the JPA 2.2 specification excerpts and the JPQL BNF quoted in the issue, then trace how @OrderBy and ORDER BY expressions are represented in this Java persistence project. Done means custom JPQL expressions are specified and supported for @OrderBy and ORDER BY, including the cited CASE/WHEN use cases.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java
- Domain
- backend-api-design, databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100