Hi,
I'm trying to implement a complex query that populates a parent object which
itself contains List objects for 5 'child' objects - i.e. 1:M relationship.
What's happening though is I'm getting back as many rows in the List objects as
exist in the child table with the most rows.
For the primary key I'm using from the parent, 4 of the child tables have 1 row
on them, 1 of the child tables has 2 rows. When I retrieve the data, all 5 of
the child objects contain 2 rows. One shows correct data (the one that
corresponds with the table containing 2 rows), the other 4 contain the same row
twice.
As I said, I'm trying to retrieve 1 parent object and as many rows as exist in
the child objects. My query therefore passes in the primary key of the parent.
<select id="getComplete" resultMap="complete-result">
SELECT t.id, t.version_id, t.fii,
ta.amount_type, ta.currency,
tbc.bargain_condition,
tc.charge_name, tc.charge_type,
tsdi.instruction_ind, tsdi.sdi1, tsdi.sdi2,
tar.acti, tar.role_in_trade
FROM trade t
LEFT OUTER JOIN trade_amount ta
ON t.id = ta.id
AND t.version_id = ta.version_id
LEFT OUTER JOIN trade_bargain_condition tbc
ON t.id = tbc.id
AND t.version_id = tbc.version_id
LEFT OUTER JOIN trade_charge tc
ON t.id = tc.id
AND t.version_id = tc.version_id
LEFT OUTER JOIN trade_sdi tsdi
ON t.id = tsdi.id
AND t.version_id = tsdi.version_id
LEFT OUTER JOIN trade_acc_role tar
ON t.id = tar.id
AND t.version_id = tar.version_id
WHERE t.id = #Id#
AND t.version_id = #VersionId#
</select>
Result map for parent is:
<resultMap id="complete-result" class="com.myapp.myTrade"
groupBy="id,versionId">
<result property="id" column="id" columnIndex="1"/>
<result property="versionId" column="version_id" columnIndex="2"/>
<result property="fii" column="fii" columnIndex="3"/>
<result property="tradeAmounts" resultMap="myTrade.get-amounts"/>
<result property="tradeCharges" resultMap="myTrade.get-charges"/>
... (for rest of child objects)
</resultMap>
Example result map for child is:
<resultMap id="get-amounts" class="com.myapp.myTradeAmount">
<result property="id" column="id"/>
<result property="versionId" column="version_id"/>
<result property="amountType" column="amount_type"/>
<result property="currency" column="currency"/>
</resultMap>
Others follow same convention, i.e. the primary key of parent is id and
version_id. Primary key of child objects is id, version_id and one other key
field (for example, amount_type above).
Note that I've removed a lot of fields for brevity. I'm also returning the
full primary key into the child objects as well as the parent.
So, what I'd expect is that for the parent, 4 of my List objects would have 1
row and one of them would have 2 rows. What I'm getting is 2 rows in all of
them.
When I run the SQL itself, 2 rows are returned as expected due to the 2 rows
existing on one of the child tables.
Can someone assist? Am I going wrong somewhere with the groupBy?
Tks,
Paul.
