I'm trying to run a select statement to retrieve records from one table based on the aggregate sum of linked records in another table, and I'm not sure how (or if) I can do this using Hibernate Criteria queries.

I am modeling a domain with Order records (in the ORDER table), which includes LineItem records (in a LINE_ITEM table). I'm looking to run a query to return order records that have any line item records where the sum of the line item amounts is bigger than some input amount.

I can run this sql statement and get the answers that I want:

select id from orders as outerourders where (
select sum(total_price) from orders inner join line_items on
orders.id = line_items.orderid and orders.id = outerorders.id
# optionally include some where clauses on the line_items attributes
group by 'orders.id'
) > 100.0

I'm not a SQL expert, and I'm a bit worried about this SQL syntax. So first off, is there a better way to do this kind of query?

Is there a way to do this with Hibernate Criteria? I've played with Subqueries and such, but can't figure out how to make it work. I can build the outer query; I can build the inner query. But I can't figure out how to add the where clause that joins the whole thing together: where the sum of the line item amounts is > 100.

Thanks for any insights,

Paul Christmann

In sql:

select o.*
from orders o, line_items li
where o.id = li.orderid
group by o.id
having sum(total_price) > 100

In hql

select o
from Order o join o.lineItems li
group by o
having sum(li.totalPrice) > 100

Try to not think in sql, seems to be similar to hql but is totally different.

Thank you for the insight on the Having clause. That definitely solved my immediate concerns about accessing the db rather than using the sub-selects that I was pondering.

Is it possible to do this with Criteria queries? We have a fair amount of code that uses Criteria-based queries. We are using a binding approach where widgets on a ui pane are tied to query "decorators" (ala a Decorator pattern), and each of those is responsible for adding just the necessary restrictions to a Criteria object.