N+1 Select in iBatis 2.2.0.638

["Jeynes, Paul [CIB-IT]" <pa...@citigroup.com>]

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.

Re: N+1 Select in iBatis 2.2.0.638

[Jeff Butler <je...@gmail.com>]

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] <pa...@citigroup.com> 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.
>