HQL Query for composite-element objects

schilm · **Joined:** Mon Jul 03, 2006 10:11 am **Posts:** 2

Hi all,

I have a Database structure that has a linking table to store metadata about the relationship.

The two tables in question are:

Data
-----
DataID int
Name varchar(100)
DataType varchar(20)

... etc.

Data_Data
------------
DataID1 int
DataType1 varchar(20)
DataID2 int
DataType2 varchar(20)
RelationshipInfo
... etc.

I have set up nHibernate using the following hbm.xml:

Code:

   <class name="MyData.Components.Data, MyData.Components" table="Data">
      <id name="ID" column="DataID" type="Int32">
         <generator class="assigned" />
      </id>
      <property name="Name" type="String" length="100" />
      <property name="DataTypeString" column="DataType" type="String" length="20" />

      <bag name="Datas1" table="Data_Data" lazy="true">
         <key column="DataID1" />
         <composite-element class="MyData.Components.DMRelationship, MyData.Components">
        <property name="DataType1" type="String" length="20" />
        <property name="DataType2" type="String" length="20" />
        <property name="RelationshipType" column="RelationshipType" type="Int32" />
            <property name="RelationshipInfo" type="String" length="2000" />
            <many-to-one name="Data" column="DataID2" class="MyData.Components.Data, MyData.Components" cascade="all"/>
         </composite-element>
      </bag>


    <bag name="Datas2" table="Data_Data" lazy="true">
         <key column="DataID2" />
         <composite-element class="MyData.Components.DMRelationship, MyData.Components">
        <property name="DataType1" type="String" length="20" />
        <property name="DataType2" type="String" length="20" />
            <property name="RelationshipType" column="RelationshipType" type="Int32" />
            <property name="RelationshipInfo" type="String" length="2000" />
            <many-to-one name="Data" column="DataID1" class="MyData.Components.Data, MyData.Components" cascade="all"/>
         </composite-element>
      </bag>
   </class>

This works fine, however I have a large amount of related items and am trying to be more efficient in the way I am querying for the related items.

I am trying to write an HQL query that will return only related data of a given DataType. However I would like to return the composite element object (DMRelationship) so I have access to the metadata as well.

If I run the following HQL I get a collection of ALL the DMRelationship objects:

Code:

SELECT DISTINCT elements(e.Datas1) FROM Data e WHERE e.ID = 100194

So I tried to pare it down by filtering on the DataType

Code:

SELECT elements(e.Datas1) FROM Data e JOIN e.Datas1 d WHERE e.ID = 100194 and d.DataType2='Issue' 

However this yields exactly the same results as above (ALL the related Datas get returned)... it appears that the elements() function adds its own join and populates the select field list off of that join. but when I try to add ANOTHER join and filter on DataType2 here the filter essentially gets ignored.

The SQL that gets executed in this cases is here (cleaned up for readability)

Code:

SELECT     dd2.DataType1, dd2.DataType2, dd2.RelationshipType, dd2.RelationshipInfo, dd2.DataID2
FROM         Data AS d 
      INNER JOIN Data_Data AS dd1 ON d.DataID = dd1.DataID1 
      INNER JOIN Data_Data AS dd2 ON d.DataID = dd2.DataID1
WHERE     (d.DataID = 100194) AND (dd1.DataType2 = 'Issue')

If I comment the first INNER JOIN and change the dd1.DataType2 to dd2.DataType2 I get the results I am looking for... however I cannot figure out how to tell nHibernate to do that for me.

I was trying to coerce nHibernate into doing what I want and I tried this:

Code:

SELECT DISTINCT d.DataType1, d.DataType2, d.RelationshipType, d.RelationshipInfo, d.Data.ID 
FROM Data e 
JOIN e.Datas1 d 
WHERE e.ID = 100194 AND d.DataType2='Issue'

which runs the exact SQL query I want it to, it filters exactly how it should, but it returns a collection of object[] values, not the DMRelationship objects that I am looking for. I could just take that object[] and turn those values into the DMRelationship object I am looking for, but that loses the linked/lazy load functionality....

I'm totally stumped on this one... can anyone offer me some guidance? or confirm my suspicions that I am SOL on this?

Thanks,
Max Schilling

wolli · **Posted:** Fri Oct 10, 2008 2:27 am

For criteria queries I can confirm that this behavior is by design:

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/querycriteria.html#querycriteria-associations

I acutally never thought about if it's the same bevahior with HQL queries but it seems so.

From an ORM point of view this behavior makes sense. Think about an object that gets loaded with a filtered collection. What should the ORM do when you insert/move/remove/ objects in the list ?

Have you thought about reversing the query:

Code:

from Data1 d where d.Data = e and d.DataType2 = 'Issue'

schilm · **Joined:** Mon Jul 03, 2006 10:11 am **Posts:** 2

Hey wolli,

Thanks for the reply!

You are right, for ORM that makes sense in this case... but for the life of me I cannot get it to return the Datas1 or Datas2 collections in a direct HQL query, so the only option I could find was to query on Data and then do the select into elements of Datas1 and/or Datas2... which then tries to create that linkage...

Ideally I would love to reverse the query... but I can't figure out how.

I tried a number of different attempts but it doesn't seem to allow me to use the Datas1 or Datas2 collections in the FROM clause...

All of these give the error: "expected: d1"

Code:

FROM Datas1 d1 WHERE d1.Data.ID = 100001
FROM Datas1 d1 WHERE d1.Data = e AND d1.DataType2 = 'Issue'

Is there possibly something special I need to do to query directly into a "composite-element" table like this and get a collection of the "composite-element" objects back?

Thanks,
Max