HQL and queries

benoitx · **Joined:** Fri Jul 08, 2005 8:55 pm **Posts:** 37

Hi

I have a question about HQL which may be basic but I cannot see any info about this:
how can I use several "where" operands for linked objects without receiving multiple resultsets.

Hibernate version: 3.0.5

Mapping documents:

Code:

<hibernate-mapping package="beans">
  <class name="MyObject" table="MYOBJECT">
    <id name="id" column="ID" type="integer" unsaved-value="-1">
      <generator class="native"/>
    </id>
    <version name="version" unsaved-value="negative" />
    <property name="name" column="NAME" type="java.lang.String"  not-null="true" />

    <set name="linkedObjects" lazy="false" cascade="all">
      <key column="MYOBJECT_ID" not-null="true"/>
      <one-to-many class="LinkedObject"/>
    </set>
  </class>
</hibernate-mapping>

<hibernate-mapping package="beans">
  <class name="LinkedObject" table="LINKEDOBJECT">
    <id name="id" column="ID" type="integer" unsaved-value="-1">
      <generator class="native"/>
    </id>
    <property name="name" column="NAME" type="java.lang.String"  not-null="true" />
    <property name="state" column="STATE" type="java.lang.String"  not-null="true" />
    <property name="myObjectId" column="MYOBJECT_ID" type="integer" insert="false" update="false"/>
  </class>
</hibernate-mapping>

Name and version of the database you are using:
MySQL 4.1

I want to return a list of MyObject for which I have some LinkedObjects with some attributes.

I am building an HQL like this:

Code:

from MyObject as mine where mine.linkedObjects.state=? and mine.linkedObjects.name=?

Unfortunately this generates TWO joins to the LINKEDOBJECT table, with the unfortunate consequence that the result is INCORRECT...

Code:

select.... from MOBJECT a, LINKEDOBJECT b, LINKEDOBJECT c
where a.ID = b.MYOBJECT_ID and b.name=? and a.ID = c.MYOBJECT_ID and c.STATE=?

If I query with name='benoit' and state='confused' I get all the name 'benoit' AND all the states 'confused' but not especially the ones that have BOTH.

How could I achieve this in HQL in a generic fashion?

Many thanks

Benoit

kochcp · **Posted:** Wed Jan 04, 2006 11:38 am

i think the dot notation is what is creating the joins on those mapped properties. You might just wanna use aliases

kochcp · **Posted:** Wed Jan 04, 2006 11:41 am

as an example

Quote:

from MyObject mine join mine.linkedObjects oo where oo.state=? and oo.name=?

benoitx · **Joined:** Fri Jul 08, 2005 8:55 pm **Posts:** 37

Thanks!

Works nicely (credited you twice for that! :-)

Benoit.

kochcp · **Posted:** Wed Jan 04, 2006 12:36 pm

your welcome

heh. didn't mean to have two posts, just forgot to put that hql example in the first one, but there is no post editing so... thanks