collection filters and Many-to-Many

knotty · **Joined:** Fri Jan 02, 2004 3:23 pm **Posts:** 3

Hi,

Here's my problem. I have an existing table structure: (simplified)

TABLE SCIENTISTS:
SC_ID

TABLE REPORTS:
RP_ID

TABLE SC_RP:
RP_ID
SC_ID
ROLE

In hibernate, I set up getReports() in Scientists, which works fine, except now I want to retrieve reports only if the role for the scientists equals("PI").

From previous posts, I can see how session.filter could help me with a one-to-many relatationship, but not a many-to-many.

Should I create a mapping for the SC_RP table, two one-to-many relationships, and forget about my many-to-many?

Thanks,
Christophe

emmanuel · **Posted:** Fri Jan 02, 2004 6:42 pm

Where did you map ROLE ? In a component ? As a map index ?
Posting mapping is easier for us to answer

knotty · **Joined:** Fri Jan 02, 2004 3:23 pm **Posts:** 3

ok, I was trying to make it simpler, but here's the mapping for the relationship table. There's an extra orID, never mind that.

You'll see a composite key. Part of the issue is, as usual, I can't touch the DB.

Code:

<?xml version='1.0' encoding='windows-1252'?>
<!DOCTYPE hibernate-mapping SYSTEM "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
<hibernate-mapping>
   <class name="gov.etc.hibernate.beans.IpOrSc" table="IPID_ORID_SCID">
  <composite-id 
        name="id"
        class="gov.etc.beans.IpOrScKey"
        >
        <key-many-to-one name="ipId" class="gov.etc.beans.IProjects" column="IPID"/>
        <key-property name="orId" type="string" column="ORID"/>
        <key-many-to-one name="scId" class="gov.etc.beans.Scientists" column="SCID"/> 
  </composite-id>

  <property name="role" type="string" column="ROLE"/>
  <property name="ryear" type="string" column="RYEAR"/>

   </class>
</hibernate-mapping>

The mapping from the Project table. Don't mind the generator, I'm not inserting records.

Code:

<?xml version='1.0' encoding='windows-1252'?>
<!DOCTYPE hibernate-mapping SYSTEM "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
<hibernate-mapping>
   <class name="gov.etc.beans.IProjects">
          <id name="id" column="IPID" type="string">
            <generator class="native"/>
          </id>
   </class>
</hibernate-mapping>

The scientists mapping:

Code:

<hibernate-mapping>

   <class name="gov.nih.cit.deca.nidb.model.hibernate.beans.Scientists">
     <id name="id" column="SCID" type="string">
       <generator class="native"/>
     </id>
     <property name="firstName" column="FNAME" type="string"/>
     <property name="lastName" column="LNAME" type="string"/>

     <bag name="ipidOridScid" lazy="false"  cascade="all" where="RYEAR='2003'">
         <key column="SCID"/>
         <one-to-many class="gov.etc.beans.IpOrSc"/>
      </bag>

    
    <bag name="reports" lazy="true" table="IPID_ORID_SCID">
      <key column="SCID"/>
      <many-to-many column="IPID" class="gov.etc.beans.IProjects"/>
    </bag>

  </class>
      
</hibernate-mapping>

So right now, I have a mapping for the relationship table, but I'm not sure I care for this model.

The problem:
Right now, when I lookup a scientist and do getReports(), I logically get all the reports for that person. I want only the reports if ROLE='PI' in the relationship table. I don't know how to do that...

In the scientists mapping, you'll notice a where="RYEAR='2003'" clause. I don't care for that either. It works, but not very flexible. This relate to the same problem. I don't want to hardcode the condition in the mapping.

Hope I'm being clear enough,

Thanks!
Christophe.