-->
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.  [ 16 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Querying against values in a collection of values
PostPosted: Fri Aug 12, 2005 6:27 am 
Newbie

Joined: Fri May 07, 2004 5:33 am
Posts: 18
I have the following mapping, which shows a class containing a set of values:

Code:
   
<class name="MyClass">
    <id name="id">
        <generator class="native" />
    </id>

    <set name="myValues">
        <key column="fk_id" />
        <element column="value" type="string" />
    </set>
</class>



I want to form a query which says:

Quote:
give me all instances of MyClass whose myValues collection contains the value "foo"


While both the online docs and the book show you how to set up a collection of values, and also how to query against the contents of a collection of entities, they don't seem to cover how to query against the contents of a collection of values.

I was hoping it would be something like this:

Code:
Criteria query = session.createCriteria(MyClass.class);
query.createAlias("myValues", "value");
query.add(Restrictions.eq("value", "foo"));


But that only works for collections of entities, not for collections of values. When i try it, I get the error:

Quote:
collection was not an association


I'm convinced I'm missing some obvious concept here, and I'd appreciate any suggestions.[/quote]


Top
 Profile  
 
 Post subject:
PostPosted: Fri Aug 12, 2005 6:39 am 
Newbie

Joined: Fri Aug 12, 2005 3:55 am
Posts: 11
Try this query:
Code:
select obj from MyObject obj where obj.myValues.foo = ?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Aug 12, 2005 6:49 am 
Newbie

Joined: Fri May 07, 2004 5:33 am
Posts: 18
avadhuta wrote:
Try this query:
Code:
select obj from MyObject obj where obj.myValues.foo = ?


Unfortunately, due to classpath restrictions (the appserver has a very old version of ANTLR), I'm limited to Criteria queries only :-(

How would i formulate the above as a criteria query?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Aug 12, 2005 7:33 am 
Newbie

Joined: Fri Aug 12, 2005 3:55 am
Posts: 11
Must be this?

Code:
Criteria query = session.createCriteria(MyClass.class);
Criteria subquery = query.createCriteria("myValues", "value");
subquery.add(Expression.eq('value', 'foo'));


Top
 Profile  
 
 Post subject:
PostPosted: Fri Aug 12, 2005 8:15 am 
Expert
Expert

Joined: Thu Sep 04, 2003 8:23 am
Posts: 368
Or you can use the option hibernate.query.factory_class to change the query parser not to use antlr and then you may use hql queries (use org.hibernate.hql.classic.ClassicQueryTranslatorFactory)

_________________
Seb
(Please don't forget to give credits if you found this answer useful :)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Aug 12, 2005 9:02 am 
Newbie

Joined: Fri May 07, 2004 5:33 am
Posts: 18
avadhuta wrote:
Must be this?

Code:
Criteria query = session.createCriteria(MyClass.class);
Criteria subquery = query.createCriteria("myValues", "value");
subquery.add(Expression.eq('value', 'foo'));


Unfortunately, that just gives the same "collection is not an association" error :-(


Top
 Profile  
 
 Post subject: querying against a collection with HQL
PostPosted: Fri Aug 12, 2005 9:34 pm 
Newbie

Joined: Thu Aug 11, 2005 1:20 pm
Posts: 8
as far as I know, Criteria queries cannot be used with Collection objects. They can only be used with entities. Which is probably why you are getting the error.

I am also looking for a solution to querying inside maps - I have been unable to do it till now. If someone knows the solution and could post it, it would be great.


Top
 Profile  
 
 Post subject: I have the same problem
PostPosted: Wed Aug 17, 2005 12:00 pm 
Newbie

Joined: Thu Sep 11, 2003 1:50 am
Posts: 6
Please fix it!


Top
 Profile  
 
 Post subject: Re: Querying against values in a collection of values
PostPosted: Wed Aug 17, 2005 9:48 pm 
Newbie

Joined: Wed Jul 13, 2005 12:24 am
Posts: 2
Quote:
give me all instances of MyClass whose myValues collection contains the value "foo"


Try this:

criteria.createAlias("myValues", "mv");
criteria.add(Expression.in("mv.value", new String[] {"foo"}));


Top
 Profile  
 
 Post subject: this should work
PostPosted: Thu Jan 19, 2006 4:51 am 
Newbie

Joined: Thu Jan 19, 2006 4:39 am
Posts: 1
> give me all instances of MyClass whose myValues collection contains the value "foo"

Code:
session.createCriteria(MyClass.class).createCriteria("myValues").add(Restrictions.idEq(foo.getId())).list();

The second createCriteria creates a subquery on items in the collection.

Maybe there is an even more efficient way to do this, in this case, please post a reply.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 24, 2006 10:17 pm 
Senior
Senior

Joined: Tue Sep 13, 2005 2:01 am
Posts: 137
I am new to Criteria.

What is the difference between Criteria.createCriteria() and createAlias()?
If a class has two collections:

class Student {

List<Course> getCourses();

List<Student> getGroup();


}

If I want to search those students who take class "Math" and "John" is his group:

shoud I use createCriteria or createAlias?

Criteria:

Criteria criteria = session.createCriteria(Student.class);
Criteria subquery1 = criteria.createCriteria("courses", course).add(Restrictions.eq(course.name, "Math"));
Criteria subquery2 = criteria.createCriteria("group", student).add(Restrictions.eq(student.name, "John"));

how to put subquery1 and subquery2 together with initial criteria?

Alias:

Criteria criteria = session.createCriteria(Student.class).
createAlias("courses", course).add(Restrictions.eq(course.name, "Math")).
createCriteria("group", student).add(Restrictions.eq(student.name, "John"));



Thanks.


Top
 Profile  
 
 Post subject: Search within Collections using criteria objects
PostPosted: Wed Oct 18, 2006 8:01 am 
Beginner
Beginner

Joined: Wed Oct 18, 2006 4:41 am
Posts: 20
Location: West Yorkshire, UK
Hello,

I have a similar query to this - I want to search within an object's collection and return the parent object if the criteria is met. I want to be able to do this using the criteria objects and use an 'in' query if possible.

I have a parent object that contains a collection of child objects. I want to pass in a collection of child objects and return the parent(s) that have children matching one or more of the passed in objects.

This is not possible using:

Code:
criteria.add(Restrictions.in("childObjectsCollection", childrenCollectionTest)


Could somebody please explain how I should go about this - I am very new to hibernate!

Many thanks in advance, Carl


Top
 Profile  
 
 Post subject: Ever find a solution to collection-of-values sub-query?
PostPosted: Thu Aug 21, 2008 5:26 pm 
Newbie

Joined: Thu Aug 21, 2008 4:53 pm
Posts: 1
Location: Richmond, BC, Canada
The answers seemed to apply to collections of entities, not values.

For the simple element case, ...

Code:
namespace Eg
{
    public class QuestionEntity
    {
        public virtual Guid ID { get; set; }
        public virtual ISet<string> StringSet { get; set; }
    }
}



and mapping

Code:
<class name="QuestionEntity">
    <id name="ID" type="Guid">
      <generator class="guid" />
    </id>
    <set name="StringSet">
      <key column="ExampleEntityID" />
      <element column="StringValue" type="String" />
    </set>
  </class>


the hql

Code:
SELECT q FROM Eg.QuestionEntity AS q WHERE EXISTS elements(q.StringSet)


will generate the SQL

Code:
select questionen0_.ID as ID30_ from QuestionEntity questionen0_ where (EXISTS(select stringset1_.StringValue from StringSet stringset1_ where questionen0_.ID=stringset1_.ExampleEntityID))


but how do you generate SQL as follows

Code:
select questionen0_.ID as ID30_ from QuestionEntity questionen0_ where (EXISTS(select stringset1_.StringValue from StringSet stringset1_ where questionen0_.ID=stringset1_.ExampleEntityID AND stringset1_.StringValue IN (?)


where ? is some parameter? (Or an equivalent join?)

I've looked everywhere and tried a bunch of things and spent an hour tracing the source code without figuring this out. I started with ICriteria and realized it wasn't possible. So I switched to hql, but still can't come up with anything good. Something like...

Code:
SELECT q FROM Eg.QuestionEntity AS q WHERE EXISTS elements(q.StringSet WHERE element IN (:stringSetParm))


... would be nice, but that's not it.

For now, I have a 'workaround' which is to replace the subquery with something like

Code:
... :parm0a in elements(q.StringSet) or :parm0b in elements(q.StringSet) or ...


It "works", but it's definitely not optimal SQL.

Any suggestions appreciated. I'm happy to adjust the mapping for the ISet, but I don't want to have to create entities to use instead of the <string>. (I know that this whole problem is not an issue if the set or other collection is a collection of entities rather than values. The issue seems to revolve around the fact that there's no way to reference the value of individual elements in order to apply select conditions. )

Thanks! :-)

_________________
Paul Miniato


Top
 Profile  
 
 Post subject: query with collection of values
PostPosted: Mon Dec 29, 2008 8:53 am 
Newbie

Joined: Mon Dec 29, 2008 8:51 am
Posts: 1
I have the same problem. Have been this resolved? Any new comments? Please...


Top
 Profile  
 
 Post subject: Criteria querying a collection of values
PostPosted: Tue Jan 13, 2009 7:17 am 
Newbie

Joined: Tue Jan 13, 2009 6:27 am
Posts: 1
Location: Norway
I'm having pretty much the same problem:

My class Element contains a Set of identities (type Long)

Code:
public class Element {
  private Set<Long> identities;
}

<class name="Element" ...
...
  <set name="identities" table="element_identities">
      <key column="element_id"/>
      <element column="identity" type="long" unique="true"/>
   </set>
...
</class>


So, when I'm trying to query for Elements which have an identity within a set, SQL would be something like:

Code:
SELECT * from element WHERE element.id IN
(
SELECT ei.element_id FROM element_identities ei
WHERE ei.identity IN (:identities)
)


I try with criteria:
Code:
Criteria crit = sess.createCriteria(Element.class, "element");
Criteria subcrit = crit.createCriteria("identities", "ident");
subcrit.add(Restrictions.in("ident", identities)));

But then I get "collection was not an association".

Attempting
Code:
Criteria crit = sess.createCriteria(Element.class, "element");
crit.add(Restrictions.in("identitites", identities)));

gives another error:
Code:
java.lang.UnsupportedOperationException: cannot perform lookups on collections


My current workaround is to execute a native SQL query to retrieve the id's of the subselect, and then a standard Restrictions.in criteria on the criteria query.

If there is any workaround or fix for this, I would be happy to know!


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 16 posts ]  Go to page 1, 2  Next

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.