ibatis-user-cs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Clough, Samuel \(USPC.PRG.Atlanta\)" <Samuel_Clo...@princetonrg.com>
Subject RE: Denormalization mapping - possible?
Date Fri, 29 Feb 2008 12:41:33 GMT
Maybe I'm missing something, but why not do a join on virtual tables
making the database do the work, and just map it right to the object
like normal.  I.e., the select would look like:

 

Select

s.StudentId,

s.GivenNames,

s.Surnam,

work.Value as WorkPhone,

home.Value as HomePhone,

mobile.Value as MobilePhone

from Student s

                left outer join (select Value from StudentContact where
ContactMethod=1) work on

                                s.StudentId = worke.StudentId

                left outer join (select Value from StudentContact where
ContactMethod=2) home on

                                s.StudentId = home.StudentId

                left outer join (select Value from StudentContact wehre
ContactMethod=3) mobile on

                                s.StudentId = mobile.StudentId

 

That would map straight to a resultmap which I'm assuming you know how
to do.  Not sure what RDMS you're working with, but this would
definitely work with MSSQL or Oracle.

 

 

From: Julian Maughan [mailto:urbica@gmail.com] 
Sent: Friday, February 29, 2008 2:34 AM
To: user-cs@ibatis.apache.org
Subject: Re: Denormalization mapping - possible?

 

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. 


Mime
View raw message