-->
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.  [ 3 posts ] 
Author Message
 Post subject: Intersection of Collections in HQL
PostPosted: Thu Nov 23, 2006 12:41 am 
Newbie

Joined: Wed Nov 22, 2006 6:36 pm
Posts: 3
Hi,

I have two objects, say ObjectA and ObjectB that both have a list of objects C. I need to display any ObjectA objects where it's list of C objects intersects with ObjectB's list of C objects.

My query looks something like this:
Code:
From ObjectA a where ? between a.date1 and a.date2 and ( (select c from a.cObjectList) in (select elements(b.cObjectList from ObjectB b where b.id =?) ) ....


This works, except for the case where ObjectA has more than one value in it's cObjectList, I then get an Oracle "subselect returned more than one value exception". I could do something like (select 1 c from...) in the first subselect , but wouldn't that not always return accurate results? Is it possible in HQL do an Any, Exists type function where the first subselect returns multiple values?

I have searched the forum thoroughly I have read the docs, specifically section 14.9 on HQL expressions, but have not had much luck in getting an answer. Does anyone have any examples of how to go about doing this sort of query? I am using Hibernate 3.2, Annotations and Oracle 10g.

Thank you.


Top
 Profile  
 
 Post subject: Collection Intersections
PostPosted: Thu Nov 23, 2006 8:44 pm 
Newbie

Joined: Mon Oct 24, 2005 6:34 pm
Posts: 14
Location: Guadalajara, Jalisco. México.
... subselect subselect returned more than one value exception would be a normal behaviour. Althoug not the necesary optimal performance, you can try that in actual java
Code:
if (  a.getCObjectList().containsAll( b.getCObjectList() )  ) {
   // something
}

... I see no way of intersecting THE WHOLE collection inside queries. Hibernate allows certan java api functionality inside HQL, would the .containsAll() work inside HQL ? you can try it ;)


Top
 Profile  
 
 Post subject: Found Solution
PostPosted: Thu Nov 23, 2006 11:11 pm 
Newbie

Joined: Wed Nov 22, 2006 6:36 pm
Posts: 3
Thanks but I found the solution I did
Code:
private String strQuery = " from ObjectA a left outer join a.ObjectCList cl where cl "+
                           " in (select elements(b.groups) from ObjectB b where "+
                           " b.userName=?) )";

This did the trick.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 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.