Querying by Scalar Collection Element?

chuggins · **Posted:** Mon Jul 17, 2006 8:45 pm

I have a class with a mapping document as follows:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
   <class name="DigitalObject" table="OBJECT_DEFINITION">
      
      <set name="childrenIds" table="OBJECT_PACK">
         <key column="PARENT_OBJECT_DEFINITION_ID" not-null="true"/>
         <element type="long" column="OBJECT_DEFINITION_ID"/>
      </set>
      
   </class>
   
</hibernate-mapping>

This is a set of scalar long values. I need to query the DigitalObject class by values of that collection. My first (successful) attempt was the following:

Code:

String hql = "from DbDigitalObject as do where :child_id in elements(do.childrenIds)";
Query q = getSession().createQuery(hql);
q.setLong("child_id", object.getObjectDefinitionId());
return (DbDigitalObject) q.uniqueResult();

The problem is that this produces SQL that makes use of a subselect for this simple query:

Code:

from OBJECT_DEFINITION dbdigitalo0_ 
where :1 in 
   (select childrenid1_.OBJECT_DEFINITION_ID from OBJECT_PACK childrenid1_ 
   where dbdigitalo0_.OBJECT_DEFINITION_ID=childrenid1_.PARENT_OBJECT_DEFINITION_ID)

I would like for it to look like the following:

Code:

from OBJECT_DEFINITION dbdigitalo0_ 
  inner join OBJECT_PACK childrenid1_ on dbdigitalo0_.OBJECT_DEFINITION_ID=childrenid1_.OBJECT_DEFINITION_ID 
where childrenid1_.OBJECT_DEFINITION_ID=:1

However, I have been unable to find much of any documentation on the subject (see http://opensource.atlassian.com/projects/hibernate/browse/HHH-869 for the best I could find). My attempt is as follows, resulting in the stack trace below.

Code:

from DbDigitalObject as do where do.childrenIds.value=:child_id

Can anyone suggest a way to do this?

Hibernate version: 3.0.5

Full stack trace of any exception that occurs:

org.hibernate.QueryException: cannot dereference scalar collection element: value [from com.pogo.db.digitalobject.DbDigitalObject as do where do.childrenIds.value=:child_id]
at org.hibernate.persister.collection.ElementPropertyMapping.toType(ElementPropertyMapping.java:33)
at org.hibernate.persister.collection.AbstractCollectionPersister.toType(AbstractCollectionPersister.java:1126)
at org.hibernate.hql.ast.FromElementType.getPropertyType(FromElementType.java:273)
at org.hibernate.hql.ast.FromElement.getPropertyType(FromElement.java:349)
at org.hibernate.hql.ast.DotNode.getDataType(DotNode.java:474)
at org.hibernate.hql.ast.DotNode.prepareLhs(DotNode.java:208)
at org.hibernate.hql.ast.DotNode.resolve(DotNode.java:166)
at org.hibernate.hql.ast.FromReferenceNode.resolve(FromReferenceNode.java:87)
at org.hibernate.hql.ast.FromReferenceNode.resolve(FromReferenceNode.java:83)
at org.hibernate.hql.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:463)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:863)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:3713)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3190)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1405)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:599)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:404)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:201)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:151)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:189)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:130)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:834)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:603)

Name and version of the database you are using: Oracle 9i

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Use "id" instead of "value" to get at the value inside an element.
You may have to explicitly join this, I'm not sure:

Code:

from DbDigitalObject do join do.childrenIds ci where ci.id = :child_id

chuggins · **Posted:** Tue Jul 18, 2006 3:46 pm

tenwit wrote:

Use "id" instead of "value" to get at the value inside an element.
You may have to explicitly join this, I'm not sure:

Code:

from DbDigitalObject do join do.childrenIds ci where ci.id = :child_id

Thank you, that did exactly what I needed! I didn't even need to explicitly join the tables. The following did the trick:

Code:

from DbDigitalObject as do where do.childrenIds.id=:child_id

If any powers-that-be are reading this, it may be worth explicitly calling this out in Chapter 14 of the manual. The symantics here seem very counter-intuitive to me, though I should have given the 'id' convention a try.

Thanks gain.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Don't call Christian a power-that-be (power-that-is?). It'll go straight to his head ;>

mithunk · **Joined:** Wed Jul 21, 2004 7:48 am **Posts:** 21

chuggins wrote:

tenwit wrote:

Use "id" instead of "value" to get at the value inside an element.
You may have to explicitly join this, I'm not sure:

Code:

from DbDigitalObject do join do.childrenIds ci where ci.id = :child_id

Thank you, that did exactly what I needed! I didn't even need to explicitly join the tables. The following did the trick:

Code:

from DbDigitalObject as do where do.childrenIds.id=:child_id

If any powers-that-be are reading this, it may be worth explicitly calling this out in Chapter 14 of the manual. The symantics here seem very counter-intuitive to me, though I should have given the 'id' convention a try.

Thanks gain.

Hi,
I have one question, For this to work, do we need to provide hbm file and class file for the scalar collection?
I mean do we need to have hbm file for childrenIds, and do we need to have a class for the collection?
I am getting an error cannot dereference scalar collection eror when I try to fild parent instances based on a value of it collection.
Please help.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

No. If you did that, it wouldn't be a scalar collection, and neither the original question nor my answer would apply. As you can see from the first post, the only mapping needed is for the entity that contains the collection of scalars. Just do what worked for chuggins, it'll work for you too.