Mapping : specify where-clause for join element.

DEGRAEFLUC · **Posted:** Mon Dec 04, 2006 12:11 pm

Hello,

I have a main table (CALL), and an additional table CTLOGG.

In order to retrieve an entry, I would use

Code:

select * from CALL where BZID01 = 1234

The additional table contains multiple entries per CALL object, but I need to map only one field of one of these entries to the CALL Instance.

Code:

select * from CTLOGG
    where LGID33 = BZID01
    and LGKD33 = "TRMOPR"

In the mapping mentioned below I do not have the possibility to specify the additional whereclause (and LGKD33 = "TRMOPR"), so it will return me too many instances (one for each CTLOGG record)

Code:

<class name="model.Call" table="CALL">
        <id name="Id" column="BZID01"/>
        <discriminator column="VMSR01" type="string"/>
        <property name="Status" type="string" column="BZFS01"/>
                
        <join table="CTLOGG" fetch="select" >
           <key column = "LGID33"/>
           <property name="TerminalOperator" type="string" column="LGDA33"/>
        </join>

Ideally the <join> element would need an additional clause :
<where clause = "LGKD33='TRMOPR'">

Does anybody know of a solution for this problem?

Thanks,

Luc De Graef

rajasaur · **Joined:** Tue Aug 23, 2005 8:52 am **Posts:** 181

You might be able to do something with filters. Check out Chapter 17. Filtering data of the Reference Doc.

DEGRAEFLUC · **Posted:** Mon Dec 04, 2006 4:48 pm

Hello,
As I read from the manual, the filter functionality can be used for classes and collections.

I would now need it inside a join element in a class.

Code:

        <join    table="CTLOGG" 
              fetch="select" 
              where=" LGKD33 = 'TRMOPR'"
              >
           <key column = "LGID33"/>
           <property name="TerminalOperator" type="string" column="LGDA33"/>
        </join>

The where clause I specified here would give me the requested functionality.
But it seems Hibernate does not support this. One would need to append this to all joined-SQL-statements...

Regards,

Luc