Hibernate criteria MAX of SUM

Question

How can I achieve this HQL with criteria (JAVA)

"SELECT MAX(pointsum) FROM (SELECT SUM(h.point) as pointsum FROM HealthPoint h WHERE h.player.partner = :partner GROUP BY h.player)"

I already test this but I got that but i got property pointsum not found

DetachedCriteria detachedPlayer = DetachedCriteria.forClass(Player.class);
detachedPlayer.add(Restrictions.eq("partner", partner));
detachedPlayer.setProjection(Projections.property("id"));

Criteria criteria = getSessionFactory().getCurrentSession().createCriteria(HealthPoint.class);
criteria.add(Subqueries.propertyIn("player", detachedPlayer));
ProjectionList proj = Projections.projectionList();
proj.add(Projections.sum("point"));
proj.add(Projections.groupProperty("player"));
criteria.setProjection(Projections.alias(proj, "pointsum"));
criteria.setProjection(Projections.max("pointsum"));
return (Integer) criteria.uniqueResult();

Thank you in advance!

Answer 1

No supported in hql

Subqueries in the from clause may not be possible in hql. see https://docs.jboss.org/hibernate/stable/core.old/reference/en/html/queryhql-subqueries.html

Note that HQL subqueries may occur only in the select or where clauses.

---

Other options

I have two suggestions to resolve this: order and limit the results, or create a view.

1. Limit the results to the one you want:

   .createQuery("SELECT SUM(h.point) as pointsum FROM HealthPoint h WHERE h.player.partner = :partner GROUP BY h.player order by pointsum desc")
   .limit(1);
   

2. Create a view in the database and select from that

   create view HealthPointSumsByPlayer 
   as 
     select sum(point), player_id 
     from Health_Points 
     group by player_id;
   

Then select (via hql) from that.