Search code examples
hibernateormhqlcriteria

Ordering results by computed value in Hibernate

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

Solution

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