Equations in "order by" using jpql or criteria api

Question

Let's assume mySweetTable has 2 columns X and Z.

How would I achieve this using jpql or criteria API ?

SELECT *, (3 * X + Z) AS OrderCondition
FROM mySweetTable 
WHERE U LIKE "as%" or V LIKE "as%" 
ORDER BY OrderCondition DESC

Answer 1

Does this answer solve your problem? Map the equation to a property on your MySweetTable entity and then order by that property.

Mapping calculated properties with JPA

Answer 2

According JPA 2 specification result variables made from scalar expression can be used in ORDER BY:

An orderby_item must be one of the following:
...
A result_variable that refers to an orderable item in the SELECT clause for which the same result_variable has been specified. This may be the result of an > > aggregate_expression, a scalar_expression, or a state_field_path_expression in the SELECT clause.
...
SELECT o.quantity, o.cost*1.08 AS taxedCost, a.zipcode
FROM Customer c JOIN c.orders o JOIN c.address a
WHERE a.state = ‘CA’ AND a.county = ‘Santa Clara’
ORDER BY o.quantity, taxedCost, a.zipcode

Consequently following should work in given case:

SELECT mye, (3 * mye.x + mye.y) AS ord
FROM MyEntity mye
WHERE mye.u LIKE 'as%' or mye.v LIKE 'as%'
ORDER BY ord DESC