Does anyone know of a way to select child records using one set of keys, but have them mapped to their parent using another set of keys.

For example, I have a set of parent records which I need to have children mapped to. I want the children to be mapped using KeyColumn1 and KeyColumn2, however I cannot select them from the database using KeyColumn1 and KeyColumn2. I know its a bad DB design but I am stuck with it. I have to select them from the database using KeyColumn3 and KeyColumn4.

I have thought of using a select to get all the parents, then using a select to get all the children (without using a bag or any other collection), but I dont think there is a way to hand the parent set, all the children and have the children associated to its respective parents.

I can post more information if someone want an exact example, but I don't want to clutter or make this post too confusing if its not necessary.

Thanks and sorry for being such a newbie.

I don't see the problem:

Your child class has primary key KeyColumn1+KeyColumn2.
In addition you have a parent-key column (or columns) which connects the child class to the parent class's primary key column(s).
Last, you have a(nother) unique key in the child class with cols 3+4.

You can search for the children on cols 3+4 as well as 1+2, and parents and children all well connected.

Maybe you need to give a more detailed example after all ...

Regards
Harald

Okay here is a more detailed example.

Parent Table:
PK Id
PK Seq
Updated_Date


Child Table:
PK Id
PK Seq -- no not FK, like I said its a bad DB design
LastName
FirstName


When I select the parent rows I select based on the Updated_Date, my keys from the child to the parent are the Id and Seq columns. I need to load all of the children in one select (per child type, total of 3 queries for the children). I have several thousand parents and 3 different types of children, so I cannot use fetch="select" because of the speed issues. Something about 10,000 trips to the database is slow :o).

What I want to do is use a fetch="subselect", but this causes invalid sql. The where clause looks like this when I use a subselect: "where (child.Id, child.Seq) in (Select parent.Id, parent.Seq from parentTable where Updated_Date > '7/19/2007')", which is not well formed sql.
I have tried using fetch = "join", but it still creates 10,000 individual selects. I have set the max_fetch_depth to 1 and isOuterJoinFetchEnabled to true, but with no luck.

So what I was trying to do with my original question was, set my keys in the hbm file, to be the Id and Seq so that my children get mapped correctly, but then select the children from the DB using only Id. I know this will result in more rows being selected than needed, but I cannot figure out any other way. The extra rows would just be thrown away when no matches are found in the parents so its not a big deal for me.

Here is my hbm file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property">
<class name="Parent" table="parentTable">
<composite-id>
<key-property name="Id" column="ID"/>
<key-property name="Code" column="Seq"/>
</composite-id>
<property name ="ModifiedDate" column="LastUpdated_Date"/>

<bag name="ChildrenA" lazy="false" fetch="subselect" table="childTable">
<key>
<column name="ID"/>
<column name="Seq"/>
</key>
<one-to-many class="ChildA"/>
</bag>
<bag name="ChildrenB" lazy="false" fetch="subselect" table="childTable">
<key>
<column name="Id"/>
<column name="Seq"/>
</key>
<one-to-many class="ChildB"/>
</bag>
</class>

<class name ="ChildA" table="childTable">
<composite-id>
<key-property name="Id" column="Id"/>
<key-property name="SequenceIdentifier" column="Seq"/>
</composite-id>

<property name="FirstName" column="FirstName"/>
<property name="LastName" column="LastName"/>
</class>

<class name ="ChildB" table="childTable">
<composite-id>
<key-property name="Id" column="Id"/>
<key-property name="SequenceIdentifier" column="Seq"/>
</composite-id>

<property name="FirstName" column="FirstName"/>
<property name="LastName" column="LastName"/>
</class>
</hibernate-mapping>

NOTE: ChildA and B are the same, but in different collections. Child C is not represented.

So unless someone has a better solution, than that is what I am trying to do.

Thanks for your help