This is an SQL issue - not an iBATIS issue. iBATIS could theoretically provide a solution to this problem, but it would require us to deal with object identity - something we don't deal with now.
In cases like this, you can only use the groupBy support for one of the embedded lists. All other lists should be populated by other select statements. This will still result in a 4N+1 query, but it can't be avoided. If you run your SQL in a client like squirrel you'll see the problem - joining all these tables together creates lots of rows with lots of duplicated data.
Jeff Butler
On 11/8/06, Jeynes, Paul [CIB-IT] <[EMAIL PROTECTED]> wrote:
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.
