Ordering results by computed value in Hibernate

Question

I have a table Player with columns id, name, wins, games_played. I mapped it to a class Player. I want to do the following query in Hibernate (preferably with Criteria, if not possible with Criteria HQL will also help)

select * from Player order by (wins / games_played)

I expect to get List<Player> sorted by their win ratio.

Thanks for the answer

Palo

score 12 · Accepted Answer · answered Mar 03 '10 at 17:11

Hibernate doesn't support arithmetics expressions in the order by clause. Quoting the section 14.12. The group by clause of the Hibernate documentation:

Neither the group by clause nor the order by clause can contain arithmetic expressions.

And indeed, the following hql query won't return properly ordered results:

select p from Player p order by (p.wins/p.gamesPlayed)

And I don't think you can divide org.hibernate.criterion.Property so the Criteria API won't solve this.

So I'd suggest to use a calculated attribute (with a formula), for example with annotations:

private float wins;
private float gamesPlayed;
@Formula(value = "WINS/GAMESPLAYED")
private float ratio;

This would allow the following query with the Criteria API:

session.createCriteria(Player.class).addOrder(Order.desc("ratio"))

This also works in .NET with fluent mapping using the Formula() method. — andypaxo, Feb 28 '11 at 18:15
A note from the future: Hibernate does now support arithmetic expressions within the order by clause: https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#hql-order-by — Stefan Haberl, Sep 23 '20 at 09:19

score 2 · Answer 2 · answered Mar 03 '10 at 10:19

2

From the Hibernate docs -

SQL functions and aggregate functions are allowed in the having and order by clauses if they are supported by the underlying database.

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-ordering

Which means you need to do it based on your underlying database.

answered Mar 03 '10 at 10:19

Padmarag

7,067
1
25
29

1

Thank you for the answer. Do you have any idea how to do this with Criteria? – Palo Mar 03 '10 at 11:18
-1: The question is about *arithmetic expressions* (and not SQL functions or aggregate functions) which are **NOT** supported in the order by. – Pascal Thivent Mar 08 '10 at 17:23

score 1 · Answer 3 · answered Sep 23 '20 at 13:43

Late to the party, but this is possible with a current version of Hibernate (5.4 in my case):

var cb = entityManager.getCriteriaBuilder();
var query = cb.createQuery(Player.class);
var root = query.from(Player.class);

query.orderBy(cb.asc(cb.quot(root.get("wins"), root.get("gamesPlayed"))));

return entityManager.createQuery(query).getResultList();

Ordering results by computed value in Hibernate

3 Answers3

Linked