Collection Property refers to join

cognus · **Joined:** Wed Nov 10, 2010 6:08 am **Posts:** 5

Hi all,

I have the problem that I need to access a collection property (in a one-to-many relationship) of another table and this one-to-many relationship refers to a join within the other property. To make it clear, here is the code:

Modul.hbm.xml:

Code:

...
<set name="mpoints" inverse="true" batch-size="100">
  <key>
    <column name="MPHX_MOD_ID" precision="38" scale="0" not-null="true" />
   </key>
   <one-to-many class="com.example.MPoints" />
</set>
...

MPoints.hbm.xml:

Code:

...
<join table="MPoints_HX" 
  subselect="select * from MPOINTS_HX where END_TIMESTAMP is null">
   <key column="MPHX_ID" />
   <many-to-one name="modul" class="com.exmaple.Modul" fetch="join" >
      <column name="MPHX_MOD_ID" precision="38" scale="0" not-null="true" />
   </many-to-one>
...

In Java I want to acces via Modul.getMpoints(). Yet this leads to an exception:

Code:

WARN  [JDBCExceptionReporter] SQL Error: 904, SQLState: 42000
ERROR [JDBCExceptionReporter] ORA-00904: "MPOINT0_"."MPHX_MOD_ID": invalid identifier

The problem is that hibernate generates a SQL statement wherein the table is joined first and afterwards the selection on MP_MOD_ID is done while this fails because MP_MOD_ID is not a column of MPoints:

Code:

select
        mpoint0_.MPHX_MOD_ID as MP2_51_4_,  [i]<-- no such column in MPOINT[/i]
        mpoint0_.ID as MP1_4_,
from
        MPOINT mpoint0_ 
    inner join
        (
            select
                * 
            from
                MPOINT_HX 
            where
                END_TIMESTAMP is null 
        ) mpoint0_1_ 
            on mpoint0_.ID=mpoint0_1_.MPHX_ID
where
        mpoint0_.MOD_ID=?  [i]<-- no such column[/i]

Does anybody knows how to handle this problem?

Thanks,
Cognus

cognus · **Joined:** Wed Nov 10, 2010 6:08 am **Posts:** 5

Is there nobody having any idea?

chirag.naikec · **Posted:** Tue Nov 23, 2010 2:24 am

<set name="mpoints" inverse="true" batch-size="100">
<key>
<column name="MPHX_MOD_ID" precision="38" scale="0" not-null="true" />
</key>
<one-to-many class="com.example.MPoints" />
</set>

As per above code you are referring to MPHX_MOD_ID in MPoints for joining the table..

cognus · **Joined:** Wed Nov 10, 2010 6:08 am **Posts:** 5

Isn't that the same code I posted? With this configuration hibernate is not able to resolve the join.

chirag.naikec · **Posted:** Tue Nov 23, 2010 4:31 am

That is what i am saying..if it is not present in table MPoint then why are you using it in join as a reference key.

<column name="MPHX_MOD_ID" precision="38" scale="0" not-null="true" /> 

Instead join on column name which is present in MPoint table.

cognus · **Joined:** Wed Nov 10, 2010 6:08 am **Posts:** 5

And this is exactly the problem. I have to join the table MPOINTS_HX with MPOINTS within Mpoints.hbm (with the subselect). now, I need to refer to the joined column MPHX_MOD_ID from the table MODUL. I know that this is a weird construct but this is the only way to handle it.

chirag.naikec · **Posted:** Tue Nov 23, 2010 5:10 am

But is there any relationship between MPoints and MPoints_HX??

cognus · **Joined:** Wed Nov 10, 2010 6:08 am **Posts:** 5

I'm not sure if I got you right. It is a one-to-many relationship between these two tables.