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@xxxxxxxxxxxxxxxxxxxxx
|
| 
