Ordering results by computed value in Hibernate

Question

Hello,

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

Answer 1

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.

Answer 2

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