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.