My model has a two 1 to n relations:
ordergroup 1-n order 1-n orderline
these relations are bidirectional, cascade=all-delete-orphan

I need a query to find all ordergroups where the total price exceeds a given amount. In regular SQL I would do something along these lines:

(ignoring the fact that MySQL 4.0 doesn't support this nesting)
select * from ordergroup where id in (
select id from ordergroup ogrp, order ordr, orderline oline where
ogrp.id=ordr.ogrp_id and
ordr.id=oline.ordr_id and
and sum(oline.price) > amount
group by ogrp.id
)

In HQL I would start out like this:
from OrderGroup ogrp where sum( ogrp.orders.orderLines.price ) > amount
but that obviously doesn't work. Perhaps I'd better use the inner query from the SQL example to fetch all the ogrp.id's and then use these id's to fetch all OrderGroups using hibernate?

What do you think?

Karel
--
(using: Hibernate 2.1.4, MySql 4.0)

it does not work because you have to use either elements() of a join clause to work on elements in collections

You can even try using your raw SQL in hibernate's createSQLQuery.

I worked around the problem by storing the redunant information,
the ordergroup objects aren't all that large.

----snippet from OrderGroup.java----

/**
* @hibernate.property
*/
getTotalPrice() {
BigDecimal total = 0;
for every order in group ..
for every line in order ...
total=total.add(line.getPrice());
return total;
}
setTotalPrice(BigDecimal tp) {
// don't do anything
}