Querying against values in a collection of values

skaffman · **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]

avadhuta · **Joined:** Fri Aug 12, 2005 3:55 am **Posts:** 11

Try this query:

Code:

select obj from MyObject obj where obj.myValues.foo = ?

skaffman · **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?

avadhuta · **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')); 

scesbron · **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)

skaffman · **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 :-(

smsea · **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.

hengels · **Joined:** Thu Sep 11, 2003 1:50 am **Posts:** 6

Please fix it!

ech · **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"}));

drsoong · **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.

javatwo · **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.

CarlHowarth · **Posted:** Wed Oct 18, 2006 8:01 am

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

miniato · **Posted:** Thu Aug 21, 2008 5:26 pm

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

paranoid · **Joined:** Mon Dec 29, 2008 8:51 am **Posts:** 1

I have the same problem. Have been this resolved? Any new comments? Please...

brandis · **Posted:** Tue Jan 13, 2009 7:17 am

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!