Is there a way to get joined-subclass to user inner joins?

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

The SQL generated by calling parent.getSubChildren() uses a left outer join. Is there any way to have it generate an inner join?

Hibernate version: 3.1

Mapping documents:

Code:

<hibernate-mapping>
  <class name="Parent" table="parent">
    <id name="id" column="id" type="long" unsaved-value="-1">
      <generator class="native"/>
    </id>
    <set name="superChildren" inverse="true" cascade="all">
      <key column="parent_id"/>
      <one-to-many class="SuperChild"/>
    </set>
    <set name="subChildren" inverse="true" cascade="all">
      <key column="parent_id"/>
      <one-to-many class="SuperChild"/>
    </set>
  </class>
  <class name="SuperChild" table="child_super">
    <id name="id" column="id" type="long" unsaved-value="-1">
      <generator class="native"/>
    </id>
    <many-to-one name="parent" class="Parent" cascade="all" outer-join="auto" update="true" insert="true" column="parent_id"/>
    <joined-subclass name="SubChild" table="child_sub">
      <key column="super_id"/>
    </joined-subclass>
  </class>
</hibernate-mapping>

The generated SQL (show_sql=true):

Code:

select subchildre0_.parent_id as parent2_1_,
subchildre0_.id as id1_,
subchildre0_.id as id32_0_,
subchildre0_.parent_id as parent2_32_0_,
case
  when subchildre0_1_.super_id is not null then 1
  when subchildre0_.id is not null then 0
end as clazz_0_
from child_super subchildre0_
left outer join child_sub subchildre0_1_ on subchildre0_.id=subchildre0_1_.super_id
where  subchildre0_.parent_id=?

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

What happens if you change the class in subChildren's set to SubChild? That might generate an inner join. The way you have it at the moment, subChildren must be selected using a left outer join, because it contains instances of SuperChild: if you switched it to inner join, then you wouldn't get an non-subclassed SuperChilds.

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

Hello again Tenwit, (http://forum.hibernate.org/viewtopic.php?t=955771)

Again you have an excellent suggestion! This does indeed generate an inner join:

Code:

select subchildre0_.parent_id as parent2_1_,
subchildre0_.super_id as super1_1_,
subchildre0_.super_id as id1_0_,
subchildre0_1_.parent_id as parent2_1_0_
from child_sub subchildre0_
inner join child_super subchildre0_1_ on subchildre0_.super_id=subchildre0_1_.id
where subchildre0_.parent_id=?

Unfortunately, table "child_sub" doesn't have a parent_id on it!

Code:

java.sql.SQLException: Unknown column 'subchildre0_.parent_id'

Gotta have a sense humour, don't ya! Ha ha!