Please help with HQL

polarbaerhh · **Posted:** Mon Jul 03, 2006 5:08 am

This is no bug, just need some help with an HQL Query.

Hibernate 3.0.5:

Mapping documents:
I have reduced them to the essentiel parts. Just some basic properties missing, version, id and so on.

ParameterSetGroup:

Code:

<hibernate-mapping>
   <class node="parametersetgroup"
   name="ParameterSetGroup"
      table="parametersetgroup">
   ......

      <map node="." name="parameterSets" lazy="true" inverse="false"
         cascade="all-delete-orphan" access="field">
         <key column="parametersetgroup_id" />
         <index column="setname_as_key" type="string" />
         <one-to-many node="parameterSet"
            class="ParameterSet" />
      </map>

   </class>

ParameterSet:

<class node="parameterset"   ParameterSet" table="parameterset">type="calendar" column="effectiveDate" not-null="true"/>
...
      <any node="referencedObject" name="referencedObject" id-type="long" meta-type="string">
...........
         <column name="ref_object_type"/>
         <column name="ref_object_id"/>
      </any>
 ....
</class>

Postgres 8.1:

my Problem:

I want to retrieve all ParameterSetGroups that hold sets with some properties. I used the following SQL Query that works well.

Code:

<sql-query name="parametercontainer.by.name.context.object">
      <![CDATA[
         select distinct {psg.*} from PARAMETERSETGROUP psg,PARAMETERSET ps where
            psg.id=ps.parametersetgroup_id
            and ps.ref_object_id=:objid
            and ps.ref_object_type=:objclass
            and psg.context=:context
            and psg.name=:groupName
      ]]>
      <return
         class="ParameterSetGroup"
         alias="psg" />
   </sql-query>

Except the fact, that polymorphic classes (properties of ParameterSet) aren't reloaded correctly.

Using HQL, this problems will vanish.

So I need an equivalent HQL query that does this job.
I tried my best, but without success. The ParameterSet has no reference to the ParameterSetGroup. Thought could do with right join, but always returns null. Any help would be very nice.

Here my last HQL try:

Code:

   <query name="parametercontainer.by.name.context.object">
      <![CDATA[
         select distinct psg from ParameterSet ps, ParameterSetGroup psg  right join fetch psg.parameterSets as parameterSets

where
             ps.referencedObject.id=:objid
            and ps.referencedObject.class.name=:objclass
            and psg.context=:context
            and psg.name=:groupName
            and parameterSets=ps

      ]]>
   </query>

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

As I understand it (see section 14.8 of the ref docs), you need to use the special a separate query for each class. However, you have provided :objclass, so you already know the class. So remove :objclass from the options, and convert it to a part of the query's name. So if one of the classes that can be the <any> is "ExampleClass", you'd have this:

Code:

<query name="parametercontainer.by.name.context.ExampleClass">
select distinct psg from ParameterSetGroup psg, ExampleClass ec
join psg.ParameterSet ps
where ps.referencedObject.class = 'ECDiscrim'
  and ec.id = ps.referencedObject.id
  and psg.context = :context
  and psg.name = :groupName
  and ec.Thingy = :parameter
</query>

I don't believe that you can refer to values in the <any> through a join, you must use a theta-style join (cross-select).