Hibernate Community • View topic - HQL restriction through mapped one-to-many property

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

HQL restriction through mapped one-to-many property

Page 1 of 1

[ 1 post ]

Previous topic | Next topic

Author

Message

ksader

Post subject: HQL restriction through mapped one-to-many property

Posted: Wed Aug 08, 2007 3:03 pm

Newbie

Joined: Tue Aug 15, 2006 3:43 pm
Posts: 5

Let me see if I can phrase this correctly,

I'm trying to restrict the results of a query based upon two properties in a mapped one-to-many collection.(Mapping files etc. after this bit)

This is the initial query that I made:

Code:

select r from Relationship r 
where
 r.indicators.lookup = :lookup 
 and r.indicators.value = :value

And this is the SQL it generated:

Code:

select
      relationsh0_.RelationshipID as Relation1_67_,
      relationsh0_.ChangedDate as ChangedD2_67_,
      relationsh0_.CreatedDate as CreatedD3_67_,
      relationsh0_.RelationshipTypeKey as Relation4_67_,
      relationsh0_.RelationshipClassKey as Relation5_67_,
      relationsh0_.RelatedID_A as RelatedID6_67_,
      relationsh0_.RelatedID_B as RelatedID7_67_ 
 from
      Relationships relationsh0_,
      RelationshipIndicators indicators1_, -- Strange
      RelationshipIndicators indicators2_  
 where
      relationsh0_.RelationshipID=indicators2_.RelationshipId 
      and relationsh0_.RelationshipID=indicators1_.RelationshipId 
      and  indicators1_.IndicatorLookup=?  -- Strange
      and indicators2_.IndicatorValue=?

I would like to understand why HQL generates this sub-optimal SQL with the strange from and restriction clause.

To get around this problem I re-wrote the HQL to be this:

Code:

select r from Relationship r join r.indicators i
where
  i.lookup = :lookup 
 and i.value = :value

Which turns out the much better:

Code:

select
  relationsh0_.RelationshipID as Relation1_67_,
  relationsh0_.ChangedDate as ChangedD2_67_,
  relationsh0_.CreatedDate as CreatedD3_67_,
  relationsh0_.RelationshipTypeKey as Relation4_67_,
  relationsh0_.RelationshipClassKey as Relation5_67_,
  relationsh0_.RelatedID_A as RelatedID6_67_,
  relationsh0_.RelatedID_B as RelatedID7_67_ 
 from
  Relationships relationsh0_ 
 inner join
  RelationshipIndicators indicators1_ 
   on relationsh0_.RelationshipID=indicators1_.RelationshipId 
 where
  indicators1_.IndicatorLookup=? 
  and indicators1_.IndicatorValue=?

I would think that the first version should turn out the same type of end SQL that the second version does, but I'm probably missing some deep mapping-comprehension.

Is this some strange side effect of one-to-many mappings?

Thanks!

Mapping files and such:

Hibernate version:3.0.4

Mapping documents:

Code:

<hibernate-mapping package="">
    <class name="Relationship" table="Relationships">
        <id name="relationshipId" type="long">
            <column name="RelationshipID" />
            <generator class="assigned" />
        </id>
        <property name="changedDate" type="timestamp">
            <column name="ChangedDate" length="19" />
        </property>
        <property name="createdDate" type="timestamp">
            <column name="CreatedDate" length="19" />
        </property>
         <many-to-one name="relationshipType" class="RelationshipType">
            <column name="RelationshipTypeKey" not-null="true" />            
        </many-to-one>
        <many-to-one name="relationshipClass" class="RelationshipClass">
            <column name="RelationshipClassKey" not-null="true" />            
        </many-to-one>
        <property name="relatedIdA" type="long">
            <column name="RelatedID_A" not-null="true" />
        </property>
        <property name="relatedIdB" type="long">
            <column name="RelatedID_B" />
        </property>
         <map name="indicators" table="RelationshipIndicators" lazy="false" cascade="delete" fetch="select">
          <key column="RelationshipId"/>
          <index column="IndicatorLookup" type="string"/>
          <one-to-many class="RelationshipIndicator"/>
       </map>
    </class>
</hibernate-mapping>

<hibernate-mapping package="">
    <class name="RelationshipIndicator" table="RelationshipIndicators">
        <id name="relationshipIndicatorKey" column="RelationshipIndicatorKey" type="long">
             <generator class="native">
            </generator>
        </id>
        <property name="relationshipId" type="java.lang.Long">
            <column name="RelationshipID" not-null="true" />
        </property>
        <property name="changedDate" type="timestamp">
            <column name="ChangedDate" length="19" />
        </property>
        <property name="createdDate" type="timestamp">
            <column name="CreatedDate" length="19" />
        </property>
        <property name="lookup" type="string">
            <column name="IndicatorLookup" length="50" not-null="true" />
        </property>
        <property name="value" type="boolean">
            <column name="IndicatorValue" length="1" not-null="true" />
        </property>
    </class>
</hibernate-mapping>

Name and version of the database you are using:MySql 5.0.x

Top

Page 1 of 1

[ 1 post ]

Board index » Hibernate & Java Persistence » Hibernate Users

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