ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeynes, Paul [CIB-IT]" <paul.jey...@citigroup.com>
Subject N+1 Select in iBatis
Date Wed, 08 Nov 2006 13:20:02 GMT

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,
       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#

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)

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"/>

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?



View raw message