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 <[EMAIL PROTECTED]> 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. > > [EMAIL PROTECTED] > > 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.
