Hibernate version:
3ish

Hi,
I've been struggling on and off with this for months :( I've finally given in and decided to ask the experts :)
I'm having big problems mapping an (inherited) DB. I've simplified the table names somewhat to avoid confusion but basically I have a 'Family' table and a Children table that has a key to the family table. I would like to retrieve for a family all the children associated with it. The problem that I have is if (for a given record in teh Children table) the child_type_id is == 1 then the record should come from the ChildType1 table, if it is a 2 then from the ChildType2 table, and if it is a 3 from the ChildType3 table.

I've tried everything defining ChildType1,2+3 as sub-types of the Class mapped to the Children table various joins, and I just can't get it a) Does anyone understand my problem and b) is this possible to solve with Hibernate 3.

I could quite easily write a method on Family such as getChildren() that performed some HQL to retrieve the relevant records but I was hoping to use cascading deletes etc. Below are my tables and the relevant id fields.. [changing the tables is unfortunately not an available option to me :( . The Child1,2+3 tables aren't really that shape but they do contain overlapping properties which are the ones I really want to extract

Any help would be gratefully appreciated!

Family
: family_id

Children
: child_id
: family_id
: child_type_id
: foreign_child_id


ChildType1
:some_id
:name
:description

ChildType2
:some_other_id
:name
:description

ChildType3
:yet_another_id
:name
:description


Thanks,
Ciaran

Sounds like you need to combine together <subclass> with nested <join> mappings.

Ooops, I misunderstood, thought you were trying to map inheritance.

Actually, you should try an <any> mapping.

Hi Gavin,
Thanks for your prompt reply. I did initially try the subclass with a nested join approach until I realised that my non primary table didn't have a key back to the primary table :(

I'm obviously being stupid, how would I use an <any> mapping in this situation. My first attempts failed horribly, but on reflection (to use my example names) Instead of trying to map a colection of Child1's,2's + 3's directly into Family, if instead I had collection of 'Children' objects , with each one containing a <any> mapping, mapped to a single object instance per child1,2 etc. Is this what you're suggesting? [sorry for not testing this first, I'm just not near anything that I could develop on]

Hello once again,
I've created the <any> mapping in Children as you suggested as follows:

But it tells me it can't find a row in the remote table (say Child1) corresponding with 'that id' (No row with the given identifier exists:). When I analyse the queries I see that @P1 is filled with a long number that I cannnot see anywhere else in my database. Does the <any> mapping work with more than just the two requisite columns in its table? [sorry if I'm doing something really stupid here, it seems so close to what I want to do! :)]
Thanks again!

Gah!! I'm so sorry, <any> worked perfectly my data turned out to be corrupt (stupid, stupid, stupid me!). I can't figure out how to delete the previous post but I guess it might help others who come this way. Thanks for all your help Gavin you pointed me straight to where I wanted to go!
- Ciaran