ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brian Yoffe" <byo...@houston.rr.com>
Subject RE: Confused with groupBy
Date Thu, 25 Aug 2005 00:24:44 GMT
I hate to hijack Alan's question, but...

I've been playing around with group by as well.   I fully understand the N+1
selects problem and understand how groupBy works.

Lets simplify your problem just a simple relationship - say account to order
(disregarding line items).

Now if an account has no orders, everything, including account drops out of
the join.   This is obviously SQL behavior, not iBatis specific.   So, I
propose to solve my SQL problem by using an outer join.   So, now I get all
accounts even those that do not have line items.    Is there a way to get
ibatis to create an empty list of orders for those accounts where there
exist no orders?

Thanks,
Brian Yoffe

-----Original Message-----
From: Larry Meadors [mailto:larry.meadors@gmail.com]
Sent: Wednesday, August 24, 2005 11:33 AM
To: user-java@ibatis.apache.org
Subject: Re: Confused with groupBy

You got it exactly right. :-)

Larry


On 8/24/05, Alan Chandler <alan@chandlerfamily.org.uk> wrote:
> Larry Meadors writes:
>
> ...
> >
> > The groupBy attribute is used to deal with the "N+1" selects problem.
> > The "N+1 Selects" problem is caused by trying to load child records
> > that are related to a list of parent records. So, if you run one query
> > to get the parent records, and there are some number "N" of them, then
> > you have to run "N" more queries to get the child records for the
> > parent records resulting in "N+1 selects".
>
> Yes I FULLY understand the above
>
>
> >
> > Here is another example that does an Account -> Order -> OrderItem
> > mapping using this technique:
> >
> >   <resultMap id="ResultAccountInfoNMap" class="AccountInfo"
> >     groupBy="account.accountId" >
> >     <result property="account.accountId" column="accountId" />
> >     <result property="orderList" resultMap="Ch7.ResultOrderInfoNMap" />
> >   </resultMap>
> >
> >   <resultMap id="ResultOrderInfoNMap" class="OrderInfo"
> >     groupBy="order.orderId" >
> >     <result property="order.orderId" column="orderId" />
> >     <result property="orderItemList" resultMap="Ch7.ResultOrderItemNMap"
/>
> >   </resultMap>
> >
> >   <resultMap id="ResultOrderItemNMap" class="OrderItem">
> >     <result property="orderId" column="orderId" />
> >     <result property="orderItemId" column="orderItemId"  />
> >   </resultMap>
> >
> >   <select id="getAccountInfoListN" resultMap="ResultAccountInfoNMap">
> >     select
> >       account.accountId as accountid,
> >       orders.orderid as orderid,
> >       orderitem.orderitemid as orderitemid
> >     from account
> >     join orders on account.accountId = orders.accountId
> >     join orderitem on orders.orderId = orderitem.orderId
> >     order by accountId, orderid, orderitemid
> >   </select>
>
> I may be a little dense here - can I just confirm.  In class
"AccountInfo",
> you have a property called "account" which is itself an object of some
> class(doesn't precisely matter, the class definition for AccountInfo will
> have imported the definition).  This sub object has a property called
> "accountID" (and similarly for "order").
>
> [only asking because this seems to make the example slightly more complex
> than it needs for explanation, and I am just making sure I understand
> correctly].
>
> >
> > Does that make it any clearer?
>
>
> Well...  I still don't understand exactly what the groupBy attribute is
> actually saying here.  Let me run one proposition by you and see if I have
> it right.
>
> Is it saying account.accountID is the common field that the query is
joined
> on, and therefore there will be a lot of records where this will be a
common
> factor - and to take all of them and use them to only create a single
> instance of class AccountInfo and to populate orderList with all the
> variants of this particular common field (and of course a similar position
> this with OrderInfo and orderItemList)?
>
> If that right, then I think I've got it.  I know when I need to use it.
>
> If not ...
>
> ... then you will have to try again:-(
>
>
>
>
>  --
> Alan Chandler
> alan@chandlerfamily.org.uk
>
>
>


Mime
View raw message