How to write Order by expression in JPQL

Question

PostgreSQL and MySQL offers to write expression into ORDER BY clause in SQL query. It allows to sort items by some column but the special values are on the top. The SQL looks like this one. ( works in Postgres )

select * from article order by id = 4, id desc;

Now I want to write it in the JPQL but it doesn't work. My attempt is:

@NamedQuery(name = "Article.special", query = "SELECT a FROM Article a ORDER BY ( a.id = :id ) DESC, a.id DESC")

This is JPA 1.0 with Hibernate driver. Application server throws this exception on deploy.

ERROR [SessionFactoryImpl] Error in named query: Article.special
org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: = near line 1, column 73 [SELECT a FROM cz.cvut.fel.sk.model.department.Article a ORDER BY ( a.id = :id ) DESC, a.id DESC]
 at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)

Thanks a lot.

Answer 1

For a named query, (ORDER BY ( a.id = :id ) or ORDER BY (:id )) won't work as DSC/ASC can't be parametrized at run-time.

1) Dynamic way if ordering element varies at runtime.

String query = "SELECT a FROM Article a ORDER BY "+orderElement+" DESC, a.id DESC";
entityManager.createQuery(query).getResultList();

2) Static way in entity bean if ordering element is fixed.

Field level:

@OrderBy("id ASC")
List<Article> articles;

Method level:

@OrderBy("id DESC")
public List<Article> getArticles() {...};