ibatis-user-cs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Julian Maughan" <urb...@gmail.com>
Subject Re: Denormalization mapping - possible?
Date Fri, 29 Feb 2008 07:33:59 GMT
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