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 Sat, 27 Aug 2005 00:14:18 GMT
No, actually I don't think it's straightforward at all - let me make my
example more concrete.

Here's an example data set:

ACCOUNTS TABLE:

ACCOUNT_ID	NAME
500		Jim
501		Bob
502		Mike


ORDERS TABLE:
ORDER_ID	ACCOUNT_ID	AMOUNT
100		500		1000
101		501		2000
102		501		3000

>From this data set, we can see that Jim has placed one order, Bob has placed
two orders, and Mike has placed no orders.   Now, I can solve this problem
quite easily if I don't mind performing N+1 selects.

My first SQL statement looks like this:

select ACCOUNT_ID, NAME from ACCOUNTS

and my second select looks like this:

select ORDER_ID, AMOUNT from ORDERS where ACCOUNT_ID=#value#.

I will not show the actual ibatis mapping, but lets assume I will use the
technique whereby I map the orders list bean property to the result of
separate select.   Again, this method WILL cause N+! selects to be
performed.   I can turn on lazy loading and they they do not all occur at
once, but N+! do occur.



So, lets assume I try to resolve the N+1 selects problem by using the
technique described below.   Now, let's consider what the SQL looks like.
I'll first try:

select a.ACCOUNT_ID, a.NAME, o.ORDER_ID, o.AMOUNT
    from ACCOUNTS a, ORDERS o
  where a.account_id = o.account_id

The result set is:
ACCOUNT_ID	NAME	ORDER_ID	AMOUNT
500		Jim	100		1000
501		Bob	101		2000
501		Bob	101		3000



Ok, so after using a regular join, Mike completely dropped out of the join
(just as we all knew it was).    Strike 2.   So, let me change the sql to an
outer join:

select a.ACCOUNT_ID, a.NAME, o.ORDER_ID, o.AMOUNT
    from ACCOUNTS a, ORDERS o
  where a.account_id = o.account_id (+)

The result set is:
ACCOUNT_ID	NAME	ORDER_ID	AMOUNT
500		Jim	100		1000
501		Bob	101		2000
501		Bob	101		3000
502		Mike	[null]		[null]

Fantastic!   I have the result set that I want.   Now, how do I keep iBatis
from bombing when it encounters the nulls and tries to map that onto an
order object.   Essentially, I want an account object created for Mike   I
expect my code to create a new list just as you recommended, Huy.

Hope that clears up my question.

Thanks,
Brian

-----Original Message-----
From: Huy Do [mailto:contactme42@gmail.com]
Sent: Wednesday, August 24, 2005 8:24 PM
To: user-java@ibatis.apache.org
Subject: Re: Confused with groupBy

Shouldn't you do that yourself in the POJO ? If you assume this behavour
in your apps, it wouldn't be a good idea to rely on ibatis to give it to
you. What if you create the pojo manually ? then you wouldn't get the
empty list. Something like

if (myList == null) {
    myList = new List()
}
return myList

is quite straightforward isnt it ?

Huy

>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