Return-Path: 
 <user-java-return-11377-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 95291 invoked from network); 13 Apr 2007 18:52:25 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 13 Apr 2007 18:52:25 -0000
Received: (qmail 99990 invoked by uid 500); 13 Apr 2007 18:52:28 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 99982 invoked by uid 500); 13 Apr 2007 18:52:28 -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 99971 invoked by uid 99); 13 Apr 2007 18:52:28 -0000
Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 13 Apr 2007 11:52:28 -0700
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=
X-Spam-Check-By: apache.org
Received-SPF: pass (herse.apache.org: local policy)
Received: from [67.153.246.93] (HELO magnolia.ociweb.com) (67.153.246.93)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 13 Apr 2007 11:52:21 -0700
Received: from localhost (magnolia.ociweb.com [10.200.1.5])
	by magnolia.ociweb.com (Postfix) with ESMTP id AA5B95571E0;
	Fri, 13 Apr 2007 13:51:59 -0500 (CDT)
Received: from magnolia.ociweb.com ([10.200.1.5])
	by localhost (magnolia [10.200.1.5]) (amavisd-new, port 10024)
	with ESMTP id 15725-02; Fri, 13 Apr 2007 13:51:58 -0500 (CDT)
Received: from [192.168.1.103] (unknown [206.80.77.71])
	(using TLSv1 with cipher AES128-SHA (128/128 bits))
	(No client certificate requested)
	by magnolia.ociweb.com (Postfix) with ESMTP id 7576F5571D0;
	Fri, 13 Apr 2007 13:51:58 -0500 (CDT)
In-Reply-To: <21929684-296C-47F6-B86B-4FC07E7B9D53@ociweb.com>
References: <21929684-296C-47F6-B86B-4FC07E7B9D53@ociweb.com>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <4072888B-EF6F-47B5-9340-A53154E79F99@ociweb.com>
Cc: Rich Reese <Rich.R.Reese@save-a-lot.com>
Content-Transfer-Encoding: 7bit
From: Mark Volkmann <mark@ociweb.com>
Subject: Re: 1:N select issue
Date: Fri, 13 Apr 2007 13:52:15 -0500
To: user-java@ibatis.apache.org
X-Mailer: Apple Mail (2.752.3)
X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at ociweb.com
X-Spam-Level: 
X-Virus-Checked: Checked by ClamAV on apache.org
X-Old-Spam-Status: No, hits=-2.6 tagged_above=-3.0 required=2.0 tests=AWL,
	BAYES_00

See the solution below.

On Apr 13, 2007, at 10:14 AM, Mark Volkmann wrote:

> I'm using groupBy to avoid a 1:N select. I've simplified what I'm  
> doing to isolate the problem.
>
> The relationships between my object model classes are as follows.
>
> A Store has
> - a storeGeneral property that is a StoreGeneralModel object
> - a phones property that is a List of PhoneModel objects
>
> A StoreGeneralModel has
> - a storeid property that is an int
> - a storename property that is a String
>
> A PhoneModel has
> - an id property that is an int
> - a phone property that is a String
>
> When I run the selectStoreByNumber select, I get back two Store  
> objects that each contain one phone number, but I should get back  
> one Store object that contains two phone numbers.
>
> Here is my fairly simple SqlMap file. Can you spot what I'm doing  
> wrong? I suspect the problem is my groupBy attribute.
>
> <?xml version="1.0" encoding="UTF-8"?>
> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
>   "http://ibatis.apache.org/dtd/sql-map-2.dtd">
>
> <sqlMap namespace="custom1">
>
>   <resultMap id="PhonesResult"
>     class="com.mycompany.myapp.model.Phones">
>     <result property="phones" resultMap="custom1.PhoneResult"/>
>   </resultMap>
>
>   <resultMap id="PhoneResult"
>     class="com.mycompany.myapp.model.PhoneModel">
>     <result column="ID" property="id" jdbcType="INTEGER"/>
>     <result column="Phone" property="phone" jdbcType="VARCHAR"/>
>   </resultMap>
>
>   <resultMap id="StoreGeneralResult"
>     class="com.mycompany.myapp.model.StoreGeneralModel">
>     <result column="StoreID" property="storeid" jdbcType="INTEGER"/>
>     <result column="StoreName" property="storename"  
> jdbcType="VARCHAR"/>
>   </resultMap>
>
>   <resultMap id="StoreResult"
>     class="com.mycompany.myapp.model.Store"
>     extends="PhonesResult" <!-- I believe this just pulls in the  
> one result element in the PhonesResult resultMap. -->
>     groupBy="storeGeneral.storeid">

Change the previous line to

       groupBy="storeGeneral">

Note that you can "group by" a property that isn't a primitive type.

>     <result property="storeGeneral"
>       resultMap="custom1.StoreGeneralResult"/>
>   </resultMap>
>
>   <select id="selectStoreByNumber"
>     resultMap="custom1.StoreResult"
>     parameterClass="java.lang.Integer">
>
>     <!-- Don't worry about the table tblPhoneAssignments.
>            I've verified by running this query through another tool  
> that I get back two rows
>            that have the same StoreID and different Phone values. -->
>     select sg.StoreID, sg.StoreName, ph.ID, ph.Phone
>     from stores..tblStoreGeneral sg
>     inner join stores..tblPhoneAssignments pa on
>       pa.ID = sg.StoreID and pa.AreaID = 1
>     inner join stores..tblPhone ph on pa.PhoneID = ph.ID
>     where sg.StoreID = #storeid#
>     order by sg.StoreID, ph.ID
>
>   </select>
>
> </sqlMap>