Problem Handling Nested Collections

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem Handling Nested Collections

Randy
Hello,

We have a nested collection, and are getting duplicates where they are
not expected.
Have tried this without success using Mybatis 3.0.1 and 3.0.4.

We have Bids which contain HourlyBids, which contain PricePairs. The
PricePairs appearing
in any given hour are being incorrectly duplicated for the same hour
in all other HourlyBids.

Below the relevant entries from our mapping file.

Bids are identified by the tuple (NODEID,DAY,BIDTYPE).
HourlyBids are identified within Bids by MKTHOUR.

Why is this not working, is there a Mybatis bug in handling multiple
<id> declarations?
Or is there some other way to make this work the way it should?

Thanks,

Randy



<select id="getBid" resultMap="bid">
        SELECT NODEID,
               DAY,
               BIDTYPE,
               NODENAME,
               MKTHOUR,
               FIXEDMW,
               PRICE,
               MW
          FROM ATABLE
      ORDER BY NODEID, DAY, BIDTYPE, MKTHOUR, MW, PRICE
</select>
<resultMap id="bid" type="Bid">
    <id property="nodeId"            column="NODEID"
jdbcType="NUMERIC"/>
    <id property="day"               column="DAY"
jdbcType="TIMESTAMP"/>
    <id property="bidType"           column="BIDTYPE"
jdbcType="VARCHAR"/>
    <result property="nodeName"      column="NODENAME"
jdbcType="VARCHAR"/>
    <collection property="hourlyBid" ofType="HourlyBid">
        <id property="mkthour"       column="MKTHOUR"
jdbcType="TIMESTAMP"/>
        <result property="fixedMw"   column="FIXEDMW"
jdbcType="NUMERIC"/>
        <collection property="pricePair" ofType="PricePair">
            <result property="price" column="PRICE"
jdbcType="NUMERIC"/>
            <result property="mw"    column="MW"
jdbcType="NUMERIC"/>
        </collection>
    </collection>
</resultMap>
Reply | Threaded
Open this post in threaded view
|

Re: Problem Handling Nested Collections

Clinton Begin
Administrator
<collection property="hourlyBid" ofType="HourlyBid">
       <id property="mkthour"       column="MKTHOUR" jdbcType="TIMESTAMP"/>
       <result property="fixedMw"   column="FIXEDMW" jdbcType="NUMERIC"/>
       <collection property="pricePair" ofType="PricePair">
           <result property="price" column="PRICE" jdbcType="NUMERIC"/>
           <result property="mw"    column="MW" jdbcType="NUMERIC"/>
       </collection>

</collection>

Your PricePair has no identity, which can cause exactly the behavior you describe.  If the data has a surrogate primary key, that's best.  Otherwise, as long as the price and mw combination is globally unique, you can set them both to id elements, like this:

       <collection property="pricePair" ofType="PricePair">
           <id property="price" column="PRICE" jdbcType="NUMERIC"/>
           <id property="mw"    column="MW" jdbcType="NUMERIC"/>
       </collection>


If you don't a surrogate primary key, and price/mw pairs are allowed duplicates in certain cases, then try joining against a sub-select (virtual/dynamic table) that includes a row number that you can use as the primary key.

Finally, if all else fails, you may consider using a sub-select -- this will introduce the N+1 selects problem, but that will only be of concern if:  1) there are hundreds or thousands of rows, 2) if the parameters used to look this up (in this case mkthour I guess) are always different.  The session cache will catch N+1 in a single session.


Try a few of those out to see if you can figure something out, and let us know.

Clinton


On Mon, Feb 14, 2011 at 3:45 PM, Randy <[hidden email]> wrote:
Hello,

We have a nested collection, and are getting duplicates where they are
not expected.
Have tried this without success using Mybatis 3.0.1 and 3.0.4.

We have Bids which contain HourlyBids, which contain PricePairs. The
PricePairs appearing
in any given hour are being incorrectly duplicated for the same hour
in all other HourlyBids.

Below the relevant entries from our mapping file.

Bids are identified by the tuple (NODEID,DAY,BIDTYPE).
HourlyBids are identified within Bids by MKTHOUR.

Why is this not working, is there a Mybatis bug in handling multiple
<id> declarations?
Or is there some other way to make this work the way it should?

Thanks,

Randy



<select id="getBid" resultMap="bid">
       SELECT NODEID,
              DAY,
              BIDTYPE,
              NODENAME,
              MKTHOUR,
              FIXEDMW,
              PRICE,
              MW
         FROM ATABLE
     ORDER BY NODEID, DAY, BIDTYPE, MKTHOUR, MW, PRICE
</select>
<resultMap id="bid" type="Bid">
   <id property="nodeId"            column="NODEID"
jdbcType="NUMERIC"/>
   <id property="day"               column="DAY"
jdbcType="TIMESTAMP"/>
   <id property="bidType"           column="BIDTYPE"
jdbcType="VARCHAR"/>
   <result property="nodeName"      column="NODENAME"
jdbcType="VARCHAR"/>
   <collection property="hourlyBid" ofType="HourlyBid">
       <id property="mkthour"       column="MKTHOUR"
jdbcType="TIMESTAMP"/>
       <result property="fixedMw"   column="FIXEDMW"
jdbcType="NUMERIC"/>
       <collection property="pricePair" ofType="PricePair">
           <result property="price" column="PRICE"
jdbcType="NUMERIC"/>
           <result property="mw"    column="MW"
jdbcType="NUMERIC"/>
       </collection>
   </collection>
</resultMap>

Reply | Threaded
Open this post in threaded view
|

Re: Problem Handling Nested Collections

randygo
This post has NOT been accepted by the mailing list yet.

Clinton,

Thanks, that is helpful and I was able to create an id for the PricePairs that renders the result I expect.

However, I am confused, because the behavior seems inconsistent. How is it that the HourlyBids get contained in their expected parent Bid, even though the mkthour id is not globally unique? In fact, we had not declared any ids in earlier versions and all of our single-level collections rendered the way we
expected. We never saw an issue until we implemented these deeper two-level collections.

Looking at the result set, it seems there is sufficient information to determine how to build the object structure, regardless of ids. If a price and mw appear in a row, then they must be contained only within
a parent object that has been rendered from the same row. We should know what HourlyBid object
represents some subset of rows, so I intuitively expect that the PricePairs for that same subset of rows
belongs to that HourlyBid.

Thanks,

Randy