Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 8574 invoked from network); 19 Feb 2007 15:47:54 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 19 Feb 2007 15:47:54 -0000 Received: (qmail 17651 invoked by uid 500); 19 Feb 2007 15:47:59 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 17644 invoked by uid 500); 19 Feb 2007 15:47:59 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 17633 invoked by uid 99); 19 Feb 2007 15:47:59 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 19 Feb 2007 07:47:59 -0800 X-ASF-Spam-Status: No, hits=2.5 required=10.0 tests=HTML_MESSAGE,NO_REAL_NAME,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: domain of Tim.Hammar@mcnichols.com designates 64.190.118.145 as permitted sender) Received: from [64.190.118.145] (HELO mail.mcnichols.com) (64.190.118.145) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 19 Feb 2007 07:47:49 -0800 Received: from dom1.mcnichols.com (unknown [10.10.10.21]) by mail.mcnichols.com (Postfix) with ESMTP id C60826E3767 for ; Mon, 19 Feb 2007 10:47:27 -0500 (EST) In-Reply-To: To: user-java@ibatis.apache.org Subject: Re: Problem with several one to many relationships in breadth MIME-Version: 1.0 X-Mailer: Lotus Notes Release 7.0.1 January 17, 2006 Message-ID: From: Tim.Hammar@mcnichols.com Date: Mon, 19 Feb 2007 10:47:17 -0500 X-MIMETrack: Serialize by Router on Dom1/HQ/McNichols(Release 7.0.1|January 17, 2006) at 02/19/2007 10:47:27 AM, Serialize complete at 02/19/2007 10:47:27 AM Content-Type: multipart/alternative; boundary="=_alternative 0056B66585257287_=" X-Virus-Checked: Checked by ClamAV on apache.org This is a multipart message in MIME format. --=_alternative 0056B66585257287_= Content-Type: text/plain; charset="US-ASCII" Hi Jeff, I'm very confused. Our application has many examples of independent lists as in Claudio's example. This worked until we implemented release 2.3. We then backed up to 2.2 with the same result and then regressed back to 2.1.7.597 which does indeed work. We will have much work to do in order to change to nested selects for the many cases where we used this feature. There must be some code differences related to this area from 2.1.7.597 to 2.2.0.638. Tim Hammar "Jeff Butler" 02/19/2007 10:36 AM Please respond to user-java@ibatis.apache.org To user-java@ibatis.apache.org cc Subject Re: Problem with several one to many relationships in breadth iBATIS groupBy support does not support two independant lists. This would be a very difficult problem to solve and would require us to deal with object identity - which we don't deal with now. So with groupBy, you can have one and only one list that is automatically populated. Other lists will need to be populated with a nested select. Jeff Butler On 2/19/07, Tim.Hammar@mcnichols.com < Tim.Hammar@mcnichols.com > wrote: Greetings Claudio, We too have this problem with the latest two releases. If you use version 2.1.7.597 you should have no problems - hopefully you have not implemented any new features like we had (and had to scramble to undo). Versions 2.2 and 2.3 will not handle what you are trying to do. I have been so swamped that I have been unable to generate a test case and submit a bug report. Maybe your example will suffice. Regards, Tim Hammar "Claudio Iacovozzi" 02/19/2007 06:51 AM Please respond to user-java@ibatis.apache.org To user-java@ibatis.apache.org cc Subject Problem with several one to many relationships in breadth We have a problem filling objects with several '1 to many relationships' in breadth from one single SQL statement. On page 40 of the iBatis Data Mapper Developer Guide the 'groupBy' construct is described as follows: "Using this approach, you can solve any N+1 problem of any depth or breadth." This just seems to work for any depth but NOT for any *breadth*. Example: An insurance agent contains a list of customers as well as a list of insured events. - The used SQL statement: - The returned result set: agentId,agent_firstname,agent_lastname,eventId,title,customerId,customer_firstname,customer_lastname {1,Agent1,Smith,1,housebreaking,1,Customer1,Johnson} {1,Agent1,Smith,4,theft,1,Customer1,Johnson} {1,Agent1,Smith,2,damage,3,Customer2,Miller} What we would like to achieve is getting a bean of type Agent, which contains two lists of all its customers and all its associated insured events: Agent1 --> List {1:Customer1, 3:Customer2} --> List {1:housebreaking, 2:damage, 4:theft} But iBatis creates a bean like this: Agent1 --> List {1: Customer1, 1:Customer1, 3: Customer2} --> List {1:housebreaking, 2:damage, 4:theft} We want to avoid several SQL statements to get the necessary data (one to get all events, and one to get all customers). Any solution ? --=_alternative 0056B66585257287_= Content-Type: text/html; charset="US-ASCII"
Hi Jeff,

I'm very confused.  Our application has many examples of  independent lists as in Claudio's example.  This worked until we implemented release 2.3.  We then backed up to 2.2 with the same result and then regressed back to 2.1.7.597 which does indeed work.  We will have much work to do in order to change to nested selects for the many cases where we used this feature.  There must be some code differences related to this area from 2.1.7.597 to 2.2.0.638.

Tim Hammar






"Jeff Butler" <jeffgbutler@gmail.com>

02/19/2007 10:36 AM
Please respond to
user-java@ibatis.apache.org
To
user-java@ibatis.apache.org
cc
Subject
Re: Problem with several one to many relationships in breadth





iBATIS groupBy support does not support two independant lists.  This would be a very difficult problem to solve and would require us to deal with object identity - which we don't deal with now.
 
So with groupBy, you can have one and only one list that is automatically populated.  Other lists will need to be populated with a nested select.
 
Jeff Butler



On 2/19/07, Tim.Hammar@mcnichols.com < Tim.Hammar@mcnichols.com > wrote:

Greetings Claudio,

We too have this problem with the latest two releases.  If you use version 2.1.7.597 you should have no problems - hopefully you have not implemented any new features like we had (and had to scramble to undo).  Versions 2.2 and 2.3 will not handle what you are trying to do.  I have been so swamped that I have been unable to generate a test case and submit a bug report.  Maybe your example will suffice.

Regards,
Tim Hammar



"Claudio Iacovozzi" <claudio.iacovozzi@googlemail.com >

02/19/2007 06:51 AM
Please respond to
user-java@ibatis.apache.org


To
user-java@ibatis.apache.org
cc
Subject
Problem with several one to many relationships in breadth






We have a problem filling objects with several '1 to many
relationships' in breadth from one single SQL statement.

On page 40 of the iBatis Data Mapper Developer Guide the 'groupBy'
construct is described as follows:
"Using this approach, you can solve any N+1 problem of any depth or breadth."

This just seems to work for any depth but NOT for any *breadth*.

Example:

An insurance agent contains a list of customers as well as a list of
insured events.

<resultMap class="agent" id="agent" groupBy="id" >
<result property="id" column="agentId" />
<result property="firstName" column="agent_firstname" />
<result property="lastName" column="agent_lastname" />
<result property="events" resultMap="event" />
<result property="customers" resultMap="customer" />
</resultMap>

<resultMap class="event" id="event" groupBy="id" >
<result property="id" column="eventId" />
<result property="title" column="title" />
</resultMap>

<resultMap class="customer" id="customer" groupBy="id" >
<result property="id" column="customerId" />
<result property="firstName" column="customer_firstname" />
<result property="lastName" column="customer_lastname" />
<result property="street" column="street" />
<result property="city" column="city" />
</resultMap>

- The used SQL statement:

<select id="getAgent" resultMap="agent" parameterClass="int">
SELECT a.agentId, a.FirstName as agent_firstname, a.LastName as
agent_lastname, b.eventId, b.title,
c.customerId, c.FirstName as customer_firstname, c.LastName as
customer_lastname, c.street, c.city

FROM insurance_agent a, agenteventlink ab, insured_event b, customer c
WHERE a.agentId = ab.agentId
AND ab.eventId = b.eventId
AND b.customerId = c.customerId
AND a.agentId = #id#

ORDER BY a.agentId,  c.customerId
</select>

- The returned result set:
agentId,agent_firstname,agent_lastname,eventId,title,customerId,customer_firstname,customer_lastname

{1,Agent1,Smith,1,housebreaking,1,Customer1,Johnson}
{1,Agent1,Smith,4,theft,1,Customer1,Johnson}
{1,Agent1,Smith,2,damage,3,Customer2,Miller}


What we would like to achieve is getting a bean of type Agent, which
contains two lists of all its customers and all its associated
insured events:

Agent1  --> List<Customer> {1:Customer1, 3:Customer2}
           --> List<Event> {1:housebreaking, 2:damage, 4:theft}
               

But iBatis creates a bean like this:
Agent1  --> List<Customer> {1: Customer1, 1:Customer1, 3: Customer2}
          --> List<Event> {1:housebreaking, 2:damage, 4:theft}


We want to avoid several SQL statements to get the necessary data (one
to get all events, and one to get all customers).

Any solution ?

--=_alternative 0056B66585257287_=--