I've written a query using Hibernate Criteria API to grab a summation of a particular value, now I need to be able to restrict the result to rows where that sum is greater or equal to a particular value.
Normally I would use a HAVING clause in my SQL to do this, but the Criteria API doesn't seem to support that at this moment.
In raw SQL this is what I need it to do:
SELECT user_pk, sum(amount) as amountSum
FROM transaction
GROUP BY user_pk
HAVING amountSum >=50;
One work-around that I thought of is to use a subquery in the FROM clause that grabs this summation value and use an outer query to restrict it using a WHERE clause.
So, in raw SQL it will look something like:
SELECT user_pk, amountSum
FROM (SELECT user_pk, sum(amount) as amountSum
FROM transaction
GROUP BY user_pk)
WHERE amountSum > 50;
Can anyone point me in the right direction as to how I could write this using Criteria API, or any other suggestions/work-arounds I can use to solve the HAVING issue?
This is the Criteria API code I have for the above example
DetachedCriteria criteria = DetachedCriteria.forClass(Transaction.class,"transaction");
criteria.setProjection(criteria.setProjection(Projections.projectionList().add(
Projections.groupProperty("user.userPK").as("user_pk")).add(
Projections.sum("transaction.amount").as("amountSum")));
Thanks!
I don't know of a way for Hibernate/NHibernate to use a subquery in the FROM clause but you can use them in the WHERE clause. Apologies for any Java/Hibernate code mistakes, I am more familiar with C#/NHibernate.
DetachedCriteria subQuery = DetachedCriteria.forClass(Transaction.class);
subQuery.setProjection(Projections.sum("amount"));
subQuery.add(Expression.eqProperty("userPk", "tOuter.userPk"));
DetachedCriteria outerQuery = DetachedCriteria.forClass(Transaction.class, "tOuter");
outerQuery.setProjection(Projections.projectionList()
.Add(Projections.sum("amount").as("sumAmount"))
.Add(Projections.groupProperty("userPk").as("user_pk"));
outerQuery.add(Subqueries.le(50, subQuery));
This code should result in SQL similar to:
SELECT tOuter.userPk as user_pk, sum(tOuter.amount) as sumAmount
FROM transaction tOuter
WHERE 50 <= (SELECT sum(amount) FROM transaction WHERE userPk = tOuter.userPk)
GROUP BY tOuter.userPk
The disadvantage of this approach is that it calculates each of the sums twice, this might have a detrimental effect on performance depending on the amount of data involved - in which case you will want to use an HQL query which does support the HAVING clause.