-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: Problems with (simple?) query.
PostPosted: Tue Jul 20, 2004 10:07 am 
Newbie

Joined: Tue Jul 20, 2004 9:35 am
Posts: 5
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)


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jul 21, 2004 8:37 am 
Hibernate Team
Hibernate Team

Joined: Sun Sep 14, 2003 3:54 am
Posts: 7256
Location: Paris, France
it does not work because you have to use either elements() of a join clause to work on elements in collections

_________________
Emmanuel


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jul 21, 2004 11:06 am 
Newbie

Joined: Sun Nov 09, 2003 12:34 pm
Posts: 15
You can even try using your raw SQL in hibernate's createSQLQuery.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jul 21, 2004 1:43 pm 
Newbie

Joined: Tue Jul 20, 2004 9:35 am
Posts: 5
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
}


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.