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