Return-Path: 
 <user-java-return-12597-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 2974 invoked from network); 14 Sep 2007 18:45:26 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 14 Sep 2007 18:45:26 -0000
Received: (qmail 10553 invoked by uid 500); 14 Sep 2007 18:45:17 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 10539 invoked by uid 500); 14 Sep 2007 18:45:17 -0000
Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-java-help@ibatis.apache.org>
List-Unsubscribe: <mailto:user-java-unsubscribe@ibatis.apache.org>
List-Post: <mailto:user-java@ibatis.apache.org>
List-Id: <user-java.ibatis.apache.org>
Reply-To: user-java@ibatis.apache.org
Delivered-To: mailing list user-java@ibatis.apache.org
Received: (qmail 10528 invoked by uid 99); 14 Sep 2007 18:45:17 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 14 Sep 2007 11:45:17 -0700
X-ASF-Spam-Status: No, hits=-0.0 required=10.0
	tests=SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: local policy)
Received: from [208.44.121.252] (HELO investoranalytics.com) (208.44.121.252)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 14 Sep 2007 18:45:15 +0000
Received: from [10.1.1.11] (HELO [10.1.1.11])
  by investoranalytics.com (CommuniGate Pro SMTP 4.3.7)
  with ESMTP id 3004600 for user-java@ibatis.apache.org;
 Fri, 14 Sep 2007 14:44:53 -0400
Message-ID: <46EAD6A6.2050306@investoranalytics.com>
Date: Fri, 14 Sep 2007 14:44:54 -0400
From: Lisa Jenkins <lisa.jenkins@investoranalytics.com>
User-Agent: Thunderbird 1.5.0.4 (Macintosh/20060516)
MIME-Version: 1.0
To: user-java@ibatis.apache.org
Subject: Re: GroupBy column insted of property =?UTF-8?B?ZG9lc27CtHQgd29y?=
 =?UTF-8?B?aw==?=
References: <71161fd20709140729o6e718044vc6662cc62ddf8139@mail.gmail.com>
	 <46EACA31.3070803@investoranalytics.com>
 <71161fd20709141126hde6e6fcyd3338b30775cb05e@mail.gmail.com>
In-Reply-To: <71161fd20709141126hde6e6fcyd3338b30775cb05e@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Checked: Checked by ClamAV on apache.org

Ah, then why have it in your select -- select 
p.person_id,p.first_name,p.last_name,a.address,a.city,a.country ??  I 
think, based on the documentation I've seen, the groupBy only works if 
the groupBy column referenced is a result property column in the 
resultMap, which may be your problem. 

from the ibatis documentation...

Avoiding N+1 Selects (1:M and M:N)

This is similar to the 1:1 situation above, but is of even greater 
concern due to the potentially large amount
of data involved.  The problem with the solution above is that whenever 
you load a Category, two SQL
statements are actually being run (one for the Category and one for the 
list of associated Products).  This
problem seems trivial when loading a single Category, but if you were to 
run a query that loaded ten (10)
Categories, a separate query would be run for each Category to load its 
associated list of Products.  This
results in eleven (11) queries total: one for the list of Categories and 
one for each Category returned to load
each related list of Products (N+1 or in this case 10+1=11).  To make 
this situation worse, we’re dealing
with potentially large lists of data.


1:N & M:N Solution

iBATIS fully solves the N+1 selects solution.  Here is an example:


<sqlMap namespace="ProductCategory">

<resultMap id=”categoryResult” class=”com.ibatis.example.Category” 
groupBy=”id”>
<result property=”id” column=”CAT_ID”/>
<result property=”description” column=”CAT_DESCRIPTION”/>
<result property=”productList” resultMap=”ProductCategory.productResult”/>
</resultMap>

<resultMap id=”productResult” class=”com.ibatis.example.Product”>
<result property=”id” column=”PRD_ID”/>
<result property=”description” column=”PRD_DESCRIPTION”/>
</resultMap>

<select id=”getCategory”  parameterClass=”int” resultMap=”categoryResult”>
select C.CAT_ID,  C.CAT_DESCRIPTION, P.PRD_ID, P.PRD_DESCRIPTION
from CATEGORY C
left outer join PRODUCT P
on C.CAT_ID = P.PRD_CAT_ID
where CAT_ID = #value#
</select>

</sqlMap>

Fábio Pisaruk wrote:
> Okay Lisa tks
>
> But remember that the bean Person doesn´t have personId property and i 
> don´t think adding it to be a good idea because person_id is an idea 
> concerning data base not my bean or the bussinesses rules.
>
> regards
>
> On 9/14/07, *Lisa Jenkins* <lisa.jenkins@investoranalytics.com 
> <mailto:lisa.jenkins@investoranalytics.com>> wrote:
>
>     person_id column is not referenced is not in the resultMap, if you add
>     that, it should work....
>
>
>     <resultMap id="resulMap-person" class="Person" groupBy="person_id">
>     <result property="personId" column="person_id"/>
>         <result property="firstName" column="first_name"/>
>         <result property="lasttName" column="last_name"/>
>         <result property="addresses" resultMap="resultMap-adress"/>
>     </resultMap>
>
>     Fábio Pisaruk wrote:
>     > Hi,
>     >
>     > Is there a way to use groupBy with a column name?
>     > I need to do so 'cause there is no property that uniquely
>     identify the
>     > bean
>     > and i am not able to change it do add one.
>     > For example:
>     > Suppose i´ve got two tables: Person and Address.
>     >
>     > Person:
>     >     person_id
>     >     first_name
>     >     last_name
>     >
>     > Address:
>     >     person_id
>     >     address_id
>     >     address
>     >     country
>     >     city
>     >
>     > And two beans:
>     > Person
>     >     firstName
>     >     lastName
>     >     addresses(Address[])
>     > Address
>     >     address
>     >     country
>     >     city
>     >
>     > My maps:
>     >
>     > <resultMap id="resulMap-person" class="Person" groupBy="person_id">
>     >     <result property="firstName" column="first_name"/>
>     >     <result property="lasttName" column="last_name"/>
>     >     <result property="addresses" resultMap="resultMap-adress"/>
>     > </resultMap>
>     >
>     > <resultMap id="resulMap-address" class="Address">
>     >     <result property="address" column="address"/>
>     >     <result property="country" column="country"/>
>     >     <result property="city" column="city"/>
>     > </resultMap>
>     >
>     > My sql:
>     >
>     > <select id="get-person-by-id" parameterClass="int"
>     > resultMap="resulMap-person">
>     >     select
>     p.person_id,p.first_name,p.last_name,a.address,a.city,a.country
>     >     from Person p ,Address a
>     >     where p.person_id=#value# and p.person_id=a.person_id
>     > </select>
>     >
>     > In doing so i am not getting the desired result.
>     > Person information are replicated for each address it contains.
>     >
>     > PS: I know two workarounds that i don´t consider good solutions:
>     > 1-) Creating a wrapperPerson with a person_id attribute and having
>     > Ibatis grouping result on it or
>     > 2-) using a nested select to get address for each person:
>     >      <result property="addresses"
>     select="get-addresses-by-person_id"
>     > column="person_id"/>
>     > <select id="get-addresses-by-person_id" parameterClass="int"
>     > resultMap="resultMap-adress">
>     >     select * from Address where person_id=#value#
>     > </select>
>     >
>     > Thanks in advance
>     >
>     > --
>     > Visto como se não executa logo a sentença sobre a má obra, o coração
>     > dos filhos dos homens está inteiramente disposto a praticar o mal.
>     >
>     >
>     > --Nerd´s sign
>     >
>     > If you have four classes, Everybody, Somebody, Anybody, and
>     Nobody, if
>     > Somebody has a bug, it could be Anybody 's fault but Nobody really
>     > knows, while Everybody shares responsibility.
>     >
>     > "Programming today is a race between software engineers striving to
>     > build bigger and better idiot-proof programs, and the universe
>     trying
>     > to build bigger and better idiots. So far, the universe is
>     winning." -
>     > Rick Cook
>
>
>
>
> -- 
> Visto como se não executa logo a sentença sobre a má obra, o coração 
> dos filhos dos homens está inteiramente disposto a praticar o mal.
>
>
> --Nerd´s sign
>
> If you have four classes, Everybody, Somebody, Anybody, and Nobody, if 
> Somebody has a bug, it could be Anybody 's fault but Nobody really 
> knows, while Everybody shares responsibility.
>
> "Programming today is a race between software engineers striving to 
> build bigger and better idiot-proof programs, and the universe trying 
> to build bigger and better idiots. So far, the universe is winning." - 
> Rick Cook