I've looked through the docs and forum but haven't been able to find guidance on this...

I have a Resource class. A resource has a homeCurrencyCode, a costPerHour and a billableRate. The costPerHour and billableRate can change over time. Each of the two values can be expressed by the user in currencies different from the Resource's homeCurrencyCode

Therefore I have a table RESOURCE that defines my resource data, including homeCurrencyCode (e.g. NZD). I have another table RESOURCE_COST that has a fk ref to the RESOURCE table's pk. The RESOURCE_COST table has data including startDate, endDate (so we know when the costs are effective) a costPerHour (number) sourceCostPerHour (number) and sourceCostCurrencyCode, and similar 3 fields for billableRate.

The intention is that my costPerHour can be defined in my home currency (NZD) but originally specified in, and converted from, another currency (e.g. USD). The costPerHour is assumed to be in the homeCurrencyCode defined for the Resource.

Internally each amount and currency code pair is stored in a Money class. The base Money and the source Money are paired into a ForeignCurrency so that the domain model has only a costPerHour containing all the informatin on it.

The Resource object contains a set of ResourceCost objects. I want the home currency code of the resource populated down into each of the ResourceCost objects so that I know that my costPerHour of NZD80 originally came from what the user specified USD50. I can't work out how to do it.

If I were doing it in straight SQL I'd do a:
select sc.srart_date, ... sc.cost_per_hour, s.home_currency_code, ...
from staff_cost sc inner join resource s on s.resource_id = sc.resource_id
which is pretty easy but I can't see which directive to use...

Any help appreciated, thatnks... Andrew

Hibernate version: 3

Mapping documents:
<hibernate-mapping package="com.ilign.ppm.domain">

<class name="Resource" table="RESOURCE" lazy="true" dynamic-update="true">

<id name="objectId" column="RESOURCE_ID" type="long">
<generator class="hilo">
<param name="table">OBJECT_ID</param>
<param name="column">next_value</param>
<param name="max_lo">100</param>
</generator>
</id>

<version name="vsn" column="VSN" type="integer"/>

<property name="firstNames" column="FIRST_NAMES" type="string"/>
<property name="lastName" column="LAST_NAME" type="string"/>
<property name="fullName" column="FULL_NAME" type="string"/>
<property name="homeCurrencyCode" column="HOME_CURRENCY_CODE" type="string"/>

<set name="staffCost" table="STAFF_COST" lazy="true" cascade="save-update">
<key column="RESOURCE_ID"/>
<many-to-many class="ResourceCost" column="STAFF_COST_ID"/>
</set>

</class>

<class name="ResourceCost" table="STAFF_COST" lazy="true" dynamic-update="true">

<id name="objectId" column="STAFF_COST_ID" type="long">
<generator class="hilo">
<param name="table">OBJECT_ID</param>
<param name="column">next_value</param>
<param name="max_lo">100</param>
</generator>
</id>

<version name="vsn" column="VSN" type="integer"/>

<property name="resource" column="RESOURCE_ID" />
<property name = "startDate" column="START_DATE" type="com.ilign.ppm.common.PersistentDateTime"/>
<property name = "endDate" column="END_DATE" type="com.ilign.ppm.common.PersistentDateTime"/>
<component name="costPerHour" class="com.ilign.ppm.common.ForeignCurrency">
<component name="baseMoney" class="com.ilign.ppm.common.Money">
<property name="amount" column="COST_PER_HOUR" type="big_decimal" not-null="true"/>
<!-- Here is where I want the homeCurrencyCode from the parent Resource -->

</component>
<component name="srcMoney" class="com.ilign.ppm.common.Money">
<property name="amount" column="SRC_COST" type="big_decimal"/>
<property name="currencyCode" column="SRC_COST_CURRENCY_CODE" type="string"/>
</component>
</component>
<component name="billableRate" class="com.ilign.ppm.common.ForeignCurrency">
<component name="baseMoney" class="com.ilign.ppm.common.Money">
<property name="amount" column="BILLABLE_RATE" type="big_decimal" not-null="true"/>
<property name="currencyCode" column="HOME_CURRENCY_CODE" type="string" not-null="true"/>
</component>
<component name="srcMoney" class="com.ilign.ppm.common.Money">
<property name="amount" column="SRC_BILLABLE" type="big_decimal"/>
<property name="currencyCode" column="SRC_BILLABLE_CURRENCY_CODE" type="string"/>
</component>
</component>
</class>
</hibernate-mapping>

I have managed to achieve what I want, but I am very dissatisfied with it. What I ended up doing in my mapping was:

What this ends up as in terms of sql generated by Hibernate is

This is the most ugly sql imaginable as it performs the subselect for every row returned. My objective was to end up with a nice clean inner join of the form:

I can't see how I can do that with any of <property>, <one-to-one>, <many-to-one> or anything else. Given Hibernate's much touted alignment with standard relational mappings, there has to be a way of assembling data for one class from a simple join of two tables. Doesn't there?

Thanks, Andrew

Skim reading, so apologies if I have missed something, but your last point:

"there has to be a way of assembling data for one class from a simple join of two tables. Doesn't there?" is correct, look at http://www.hibernate.org/hib_docs/v3/re ... ation-join.

HTH.

That is so, but according to my mapping definition (3.0) a join tag is only allowed inside a class or subclass tag. As shown in my posted mapping above the place I need the definition is inside a component tag.

In the end what I have done is change the data model and pushed the currency code attributes down from my resource table to my resource cost table. The original idea was to prevent duplication of date unnecessarily in the many-side of the relationship, but the reality of the situation is that it isn't much data and there are relatively few resource_cost rows (maybe in the tens) envisioned for each resource.