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

Allaallah

Answer 1

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"))

Lazulite

This also works in .NET with fluent mapping using the Formula() method. – Sammer

A note from the future: Hibernate does now support arithmetic expressions within the order by clause: docs.jboss.org/hibernate/orm/5.4/userguide/html_single/… – Renowned

Answer 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.

Gunas

Thank you for the answer. Do you have any idea how to do this with Criteria? – Allaallah

-1: The question is about arithmetic expressions (and not SQL functions or aggregate functions) which are NOT supported in the order by. – Lazulite

Answer 3

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();

Renowned