Hi,
I have the following scenario:

Tables:
trade(trade_id,trade_info_id,trade_version,valid_from_valid_to,....)
trade_info(trade_info_id,trade_info_version,valid_from_valid_to,....)
trade_sent_status(trade_info_id,trade_info_version,trade_version,status,system)

The relationship is every trade has a trade_info
Now we have the 3rd table trade_sent_status which contains information abt which version of trade and trade_info was sent to which downstream system.

Now i want to ensure that the latest version of trade_sent_status is part of the trade object

In the xml for trade_sent_status table we have defined a component:

<component name="latestId" class="com.hibernate.generated.TradeSentStatusId" >
<property name="pfTradeVersion" type="int" not-null="false" insert="false" update="false">
<column name="pf_trade_version" />
</property>
<property name="tradeInfoVersion" type="int" not-null="false" insert="false" update="false">
<column name="trade_info_version" />
</property>
<property name="pfTradeId" type="int" not-null="false" insert="false" update="false">
<column name="pf_trade_id" />
</property>
</component>

And now in trade i tried something like this
<one-to-one name="tradeInfo" class="com.hibernate.generated.TradeInfo" property-ref="pfTradeId" fetch="join" lazy="false" >
<formula>pf_trade_id</formula>
</one-to-one>

<one-to-one name="latestSentStatus" class="com.hibernate.generated.TradeSentStatus" property-ref="latestId" fetch="join" lazy="false" >
<formula>pf_trade_version</formula>
<formula>tradeInfo.version</formula>
<formula>pf_trade_id</formula>
</one-to-one>

But this fails and throws exception saying tradeInfo.version is invalid cloumn reference!

Any help is highly appreciated.
Thanks

I could get over the issue by defining a select query in the formula element so essentially my mapping looks like:

<one-to-one name="latestSentStatus" class="com.hibernate.generated.TradeSentStatus" property-ref="latestId" fetch="join" lazy="false">
<formula>pf_trade_version</formula>
<formula>(select max(ti.version) from trade_info ti where ti.pf_trade_id=pf_trade_id group by ti.pf_trade_id )</formula>
<formula>pf_trade_id</formula>
</one-to-one>

But now the issue is there are some date filters being used which are bypassed and it fails regression testing.