ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: Problem with several one to many relationships in breadth
Date Mon, 19 Feb 2007 15:36:25 GMT
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.2and
> 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 ?
>
>

Mime
View raw message