Hi,

I need to write a HQL query. The HQL query mainly contains OUTER JOIN between superclass and subclass. Because we want to get property belonging to both superclass as well subclass. The superclass and subclass relation have been implemented as "table per hierarchy".


The relationship between objects are something like this:

Object1 => a separate class/table
<class name="Object1" table="Object1">
<property name="property1" column="property1_col" />""
<many-to-one name="superclass" column="superclass_id" not-null="true" />
</class>


SuperClass, SubClass1 => these are implemented as "table per class hierarchy"
<class name="SuperClass" table="SuperClass">
<property name="property2" column="property2_col" />

<subclass name="SubClass1" discriminator-value="SubClass1">
<many-to-one name="sub-property">
<column name="sub_property_id" index="" />
</many-to-one>
</subclass>

<subclass name="SubClass2" discriminator-value="SubClass2">
</subclass>
</class>


Now I've to write an HQL query equal to following SQL query:

SELECT object1Item.property1, superclassItem.property2, superclassItem.sub_property_id
FROM Object1 as object1Item, SuperClass as superclassItem
WHERE object1Item.superclass_id = superclassItem.id and object1Item.property1 like '%test%'

(Here we have to take all the rows from SuperClass table including rows pointing to SubClass1. That means SuperClass rows belonging to some other subclasses should also be taken into account (or) we can say OUTER JOIN between SuperClass and SubClass1)

I was working on possible HQL queries equivalent to above SQL one that could be used to list the cellRule fields along with SKU name for Redemption cell rules only. Some of them are as follows:

1)
SELECT object1Item.property1, superclassItem.property2 , superclassItem. sub-property
FROM Object1 as object1Item, SuperClass as superclassItem
WHERE object1Item.superclass = superclassItem and object1Item. property1 like '%test%'

== Hibernate does not accept this query and gives an error message saying "sub-property is not a property of SuperClass class". Hibernate considers "sub-property" as property of 'SubClass1' subclass instead of Superclass.


2)
SELECT object1Item.property1, subclassItem.property2 , subclassItem. sub-property
FROM Object1 as object1Item, SubClass as subclassItem
WHERE object1Item.superclass = subclassItem and object1Item. property1 like '%test%'

== This HQL query will include only SubClass rows and thus does not give all result rows.


3)
SELECT object1Item.property1, superclassItem.property2 , subclassItem. sub-property
FROM Object1 as object1Item, SuperClass as superclassItem, SubClass as subclassItem
WHERE object1Item.superclass = superclassItem and object1Item. property1 like '%test%'

== This HQL query gives all the result rows but is inefficient from performance point of view as we are including SuperClass table twice without any join condition.


Plz give me your feedback on this.
Thanks in advance