Parent/child joins with three children

RCarter · **Joined:** Thu Mar 16, 2006 12:06 pm **Posts:** 6

Hibernate version: 3.2

Mapping documents (excerpt):
Parent.xbm.xml:

Code:

<hibernate-mapping>
    <class name="Parent" table="parent">
        <id name="id" type="int">
            <column name="id" />
       <generator class="assigned" />
        </id>
        <set name="firstChildren" inverse="true">
            <key>
                <column name="parent_id" not-null="true"/>
            </key>
            <one-to-many class="FirstChild" />
        </set>
        <set name="secondChildren" inverse="true">
            <key>
                <column name="parent_id not-null="true"/>
            </key>
            <one-to-many class="SecondChild" />
        </set>
        <set name="thirdChildren" inverse="true">
            <key>
                <column name="parent_id not-null="true"/>
            </key>
            <one-to-many class="ThirdChild" />
        </set>
    </class>
</hibernate-mapping>

FirstChild.xbm.xml:

Code:

<hibernate-mapping>
    <class name="FirstChild" table="first_child">
        <id name="id" type="int">
            <column name="id" />
            <generator class="assigned" />
        </id>
        <many-to-one name="parent" class="Parent" fetch="select">
            <column name="parent_id" not-null="true">
            </column>
        </many-to-one>
        <property name="month" type="date">
            <column name="month" length="10" not-null="true">
            </column>
        </property>
        <property name="value" type="big_decimal">
            <column name="value" precision="12" not-null="true">
                <comment></comment>
            </column>
        </property>
    </class>
</hibernate-mapping>

SecondChild and ThirdChild are similar to FirstChild

Tables content are something like that:

Code:

Parent:
ID
1
2
3

FirstChild:
parent_id  month    value
1          2006-12   10
1          2006-11   20
1          2006-09   30
2          2006-11   15

SecondChild:
parent_id  month    value
1          2006-09   10
1          2006-08   53
1          2006-08   34
2          2006-10   6

and so on...

I've tried and tried and googled and searched but I still can't do a query that generates an output like that

Code:

parent_id  first_child_month  second_child_month  third_child_month  first_child_value second_child_value third_child_value
1          2006-12            null                null                10                null               null
1          2006-11            null                null                20                null               null
1          2006-09            2006-09             null                30                10                 null
2          null               2006-10             2006-10             null              6                  12

I hope this example is something clear... I want a record for every month and every parent with the associated first, second, third children if they are present.
With only one child I could do a left join, but with three children?
I solved temporary doing three different queries (using left join) and then manipulating children (I often have to do a sum) with Java code, but I would like to know a better solution.

Thank you very much,

Bye

batmat · **Posted:** Tue Dec 19, 2006 2:42 pm

Well, are you sure this is an Hibernate problem? Isn't it more a SQL one?

Are you forced to work with "records" or things like that, can't you work at the object level. Couldn't you explain what you want to do, say the sum of the values of the secondChildren set? This way I guess you'll have more answers 'cause more people are used to speak about objects, I guess.

Cheers

RCarter · **Joined:** Thu Mar 16, 2006 12:06 pm **Posts:** 6

You are right, and I just found that I posted to wrong forum... I'm really sorry.

Quote:

Couldn't you explain what you want to do, say the sum of the values of the secondChildren set?

Sometimes I have to sum the values of the secondChildren set, sometimes the values of the firstChildren, plus the value of the secondChildren, plus the value of the third and doing various filtering.

Speaking about object, I need a list of first, second and third children that have the same month and are associated with the same parent, and if in some months there isn't a child, I need the other two (or one).

Thank you for your answer and sorry for my mistakes.

Bye