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:[EMAIL PROTECTED]
Sent: Wednesday, August 24, 2005 8:24 PM
To: [email protected]
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:[EMAIL PROTECTED]
>Sent: Wednesday, August 24, 2005 11:33 AM
>To: [email protected]
>Subject: Re: Confused with groupBy
>
>You got it exactly right. :-)
>
>Larry
>
>
>On 8/24/05, Alan Chandler <[EMAIL PROTECTED]> 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
>>[EMAIL PROTECTED]
>>
>>
>>
>>
>>
>
>
>
>
