From user-cs-return-2774-apmail-ibatis-user-cs-archive=ibatis.apache.org@ibatis.apache.org Fri Feb 29 07:34:32 2008 Return-Path: Delivered-To: apmail-ibatis-user-cs-archive@www.apache.org Received: (qmail 32016 invoked from network); 29 Feb 2008 07:34:32 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 29 Feb 2008 07:34:32 -0000 Received: (qmail 44649 invoked by uid 500); 29 Feb 2008 07:34:26 -0000 Delivered-To: apmail-ibatis-user-cs-archive@ibatis.apache.org Received: (qmail 44629 invoked by uid 500); 29 Feb 2008 07:34:26 -0000 Mailing-List: contact user-cs-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-cs@ibatis.apache.org Delivered-To: mailing list user-cs@ibatis.apache.org Received: (qmail 44618 invoked by uid 99); 29 Feb 2008 07:34:26 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 28 Feb 2008 23:34:26 -0800 X-ASF-Spam-Status: No, hits=2.0 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of urbica@gmail.com designates 66.249.92.168 as permitted sender) Received: from [66.249.92.168] (HELO ug-out-1314.google.com) (66.249.92.168) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 29 Feb 2008 07:33:51 +0000 Received: by ug-out-1314.google.com with SMTP id e2so158715ugf.21 for ; Thu, 28 Feb 2008 23:33:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; bh=am1FjW9WLiMg5PaR42j3vY+8Xr88wE+5lYNPoS0DYws=; b=Et1tght9S3wMiwDpvSse8dZQmaVGtIeEHrb1PV9CmBPabDyFd99La17BlBIpZGOZ/syQqrZp+sW/oqjV+uaQH+oPyNMKHjfoUmmUmM6v2PT6SxXtCENW1DX6o6jbwWn74btQcCe8QowsOSPfR0azO1/fEZBRT3JmNIXQmWKAOW0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=KadNOHbnxVVDfKTgVyQfSfjWVzO6Tih82bhaRF20wqB6phmF4cLkUBjZ2tt29vbXkRwefAnL6tNVH7FYS6lueGwGVqIv48Hjk01ocK0blbw/8KI9YHUN4B8TzY6v29CEAu7UiZRYiENp00dXuiMy8lPT2/N/7x5+KYl51bBmoaI= Received: by 10.78.172.20 with SMTP id u20mr9583458hue.12.1204270439116; Thu, 28 Feb 2008 23:33:59 -0800 (PST) Received: by 10.78.181.2 with HTTP; Thu, 28 Feb 2008 23:33:59 -0800 (PST) Message-ID: <8395ee730802282333ya2a87a8n60a07b241dfdcb7b@mail.gmail.com> Date: Fri, 29 Feb 2008 16:33:59 +0900 From: "Julian Maughan" To: user-cs@ibatis.apache.org Subject: Re: Denormalization mapping - possible? In-Reply-To: <53C62391-D3CC-4A3C-94CC-6714A844A4BA@piasecki.name> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2566_20381264.1204270439173" References: <8395ee730802281709s379bff3h89ddbda9e8e948a0@mail.gmail.com> <8395ee730802282017t5b4a54ak455789edad4946f3@mail.gmail.com> <69AC4E98-4808-446E-A831-FADC38CF2FCA@piasecki.name> <53C62391-D3CC-4A3C-94CC-6714A844A4BA@piasecki.name> X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_2566_20381264.1204270439173 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Nicholas I was hoping that IBatis had a way of doing this that wouldn't require a SELECT on the StudentCourse table for each contact method type. Even with your second approach (with the sub-select), it requires multiple selects. Ideally I would want to do a single query (SELECT ContactMethod, Value FROM StudentCourse WHERE StudentID = ?) and then map this to the Student object. I actually thought this would be a scenario that IBatis could deal with quite easily. Perhaps the only way to achieve what I want is to resort to code (QueryWithRowDelegate, as you said). Many thanks for your input into this...at least I know I'm probably not missing something obvious. Regards Julian On 29/02/2008, Nicholas Piasecki wrote: > > And of course now that I think about it, if your database supports sub- > selects, you could do it all in one go. That would be easier and less > crazy than the e-mail I just shot out. Something like ... > > SELECT > (SELECT Value FROM StudentContact WHERE ContactMethod=1 AND > StudentId=#value#) AS WorkPhone, > StudentId, > GivenName, > FROM Student > WHERE StudentId = #value# > > > On Feb 29, 2008, at 12:20 AM, Nicholas Piasecki wrote: > > > For a no code solution, I think you can achieve what you want via a > > slight perversion of resultMapping. Something like (warning: > > untested/pseudocode ahead)... > > > > > > > column="ContactMethod=WorkPhoneContactMethod, StudentId=StudentId" > > select="SelectPhoneNumberByContactMethodAndStudentId" /> > > > column="ContactMethod=WorkPhoneContactMethod, StudentId=StudentId" > > select="SelectPhoneNumberByContactMethodAndStudentId" />\ > > > column="ContactMethod=WorkPhoneContactMethod, StudentId=StudentId" > > select="SelectPhoneNumberByContactMethodAndStudentId" /> > > > > > > > > > > > > > resultClass="string"> > > SELECT > > Value > > FROM StudentContact > > WHERE ContactMethod=#ContactMethod# AND > > StudentId = #StudentId# > > > > > > This is based on the documentation at > http://ibatis.apache.org/docs/dotnet/datamapper/ch03s05.html#id384072 > > . > > > > This may or may not be an insane solution or might not work, so > > we'll see if others chime in. If it doesn't work, I suppose you > > could always do some data wrangling in a QueryWithRowDelegate call. > > > > Hope this helps! > > > > V/R, > > Nicholas Piasecki > > > > Software Developer > > Skiviez, Inc. > > nick@skiviez.com > > 804-550-9406 > > > > On Feb 28, 2008, at 11:17 PM, Julian Maughan wrote: > > > >> Could anyone explain if the following can be done in IBatis.Net: > >> > >> I have two tables... > >> > >> 1) Student > >> - StudentId (int) > >> - GivenNames (varchar) > >> - Surname (varchar) > >> > >> 2) StudentContact > >> - StudentContactId (int) > >> - Value (varchar), ie. a phone number > >> - ContactMethod (int), eg. 1 = WorkPhone, 2 = HomePhone, 3 = > >> MobilePhone, etc.. > >> - StudentId (int) - foreign key to Student, allowing many > >> StudentContacts per Student > >> > >> I want to map these tables to a class as follows: > >> > >> Class Student > >> Private _studentId As Integer > >> Private _givenNames As String > >> Private _surname As String > >> Private _workPhone As String > >> Private _homePhone As String > >> Private _mobilePhone As String > >> End Student > >> > >> As you can see, I want to use the mapping to 'denormalize' the > >> contact details on the Student class. I could easily map to an > >> IList(Of String) property instead, but I was hoping the mapper > >> would allow me to design my classes the way I want them... > >> > >> Thanks > >> Julian > > > > -- Nothing can have as its destination anything other than its origin. The contrary idea, the idea of progress, is poison. ------=_Part_2566_20381264.1204270439173 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Nicholas

I was hoping that IBatis had a way of doing this that wouldn't require a SELECT on the StudentCourse table for each contact method type. Even with your second approach (with the sub-select), it requires multiple selects. Ideally I would want to do a single query (SELECT ContactMethod, Value FROM StudentCourse WHERE StudentID = ?) and then map this to the Student object.

I actually thought this would be a scenario that IBatis could deal with quite easily. Perhaps the only way to achieve what I want is to resort to code (QueryWithRowDelegate, as you said). Many thanks for your input into this...at least I know I'm probably not missing something obvious.

Regards
Julian

On 29/02/2008, Nicholas Piasecki <nicholas@piasecki.name> wrote:
And of course now that I think about it, if your database supports sub-
selects, you could do it all in one go. That would be easier and less
crazy than the e-mail I just shot out. Something like ...

SELECT
        (SELECT Value FROM StudentContact WHERE ContactMethod=1 AND
StudentId=#value#) AS WorkPhone,
        StudentId,
        GivenName,
FROM Student
WHERE StudentId = #value#


On Feb 29, 2008, at 12:20 AM, Nicholas Piasecki wrote:

> For a no code solution, I think you can achieve what you want via a
> slight perversion of resultMapping. Something like (warning:
> untested/pseudocode ahead)...
>
> <resultMap id="ResultMapStudent">
>       <result property="WorkPhone"
> column="ContactMethod=WorkPhoneContactMethod, StudentId=StudentId"
>               select="SelectPhoneNumberByContactMethodAndStudentId" />
>       <result property="HomePhone"
> column="ContactMethod=WorkPhoneContactMethod, StudentId=StudentId"
>               select="SelectPhoneNumberByContactMethodAndStudentId" />\
>       <result property="MobilePhone"
> column="ContactMethod=WorkPhoneContactMethod, StudentId=StudentId"
>               select="SelectPhoneNumberByContactMethodAndStudentId" />
>       <!-- Other Properties -->
> </result>
>
> <select id="GetStudents" resultMap="ResultMapStudent">
>       SELECT
>               1 AS WorkPhoneContactMethod,
>               2 AS HomePhoneContactMethod,
>               3 AS MobilePhoneContactMethod
>               StudentId,
>               GivenNames,
>               Surname
>       FROM Student
> </select>
>
> <statement id="SelectPhoneNumberByContactMethodAndStudentId"
> resultClass="string">
>       SELECT
>               Value
>       FROM StudentContact
>       WHERE ContactMethod=#ContactMethod# AND
>       StudentId = #StudentId#
> </statement>
>
> This is based on the documentation at http://ibatis.apache.org/docs/dotnet/datamapper/ch03s05.html#id384072
> .
>
> This may or may not be an insane solution or might not work, so
> we'll see if others chime in. If it doesn't work, I suppose you
> could always do some data wrangling in a QueryWithRowDelegate call.
>
> Hope this helps!
>
> V/R,
> Nicholas Piasecki
>
> Software Developer
> Skiviez, Inc.
> nick@skiviez.com
> 804-550-9406
>
> On Feb 28, 2008, at 11:17 PM, Julian Maughan wrote:
>
>> Could anyone explain if the following can be done in IBatis.Net:
>>
>> I have two tables...
>>
>> 1) Student
>> - StudentId (int)
>> - GivenNames (varchar)
>> - Surname (varchar)
>>
>> 2) StudentContact
>> - StudentContactId (int)
>> - Value (varchar), ie. a phone number
>> - ContactMethod (int), eg. 1 = WorkPhone, 2 = HomePhone, 3 =
>> MobilePhone, etc..
>> - StudentId (int) - foreign key to Student, allowing many
>> StudentContacts per Student
>>
>> I want to map these tables to a class as follows:
>>
>> Class Student
>> Private _studentId As Integer
>> Private _givenNames As String
>> Private _surname As String
>> Private _workPhone As String
>> Private _homePhone As String
>> Private _mobilePhone As String
>> End Student
>>
>> As you can see, I want to use the mapping to 'denormalize' the
>> contact details on the Student class. I could easily map to an
>> IList(Of String) property instead, but I was hoping the mapper
>> would allow me to design my classes the way I want them...
>>
>> Thanks
>> Julian
>




--
Nothing can have as its destination anything other than its origin. The contrary idea, the idea of progress, is poison. ------=_Part_2566_20381264.1204270439173--