Re: Mapped statement with chained queries ?

[Nathan Maves]

Although your solution will work, I would suggest another.  With your way you run into the classic n+1 problem.  One query to get the tasks, then n number of queries to get the jobs for those tasks.  You get the point.  That is why this is not the preferred way. Try to use the build in group by functionality in iBatis.  This way you only have to write one query that uses standard joins.  In your case you might have to use some outer joins to ensure that you get all tasks without jobs and all jobs without clients.  Then use the group by attribute in you result map.  Look to page 36 of the developers guild for an example of this functionality. Cheers, Nathan On Jul 21, 2006, at 8:54 AM, Debasish Dutta Roy wrote: You can do this in a simple manner. In the resultmap you need to provide the values properly. Here is an example. Here is the resultmap for Task <resultMap id="taskMap" class="com.myproject.Task ">       <result property="jobId" column="jobid" select="getJob"/>       ---- other properties --- </resultMap> <select id="getTask" resultMap="taskMap">    --- Your select for task --- </select> <resultMap id="jobMap" class="com.myproject.Job">       <result property="clientId" column="clientId" select="getClient">      --- other properties --- </resultMap> <select id="getJob" resultMap="jobMap">    -- select statement for getting job info-- </select> <resultMap id="clientMap" class=" com.myproject.Client">       -- a normal ma with client bean properties mapped against columns -- </resultMap> <select id="getClient" resultMap="clientMap">    -- select statement for getting client info-- </select> This way you will only call getTask from your client and iBATIS will do the rest. You will save on database trips. Whatever you need to specify in #value# of 2nd and 3rd statement is taken from the "column" attribute. In this case jobId for 2nd select and "clientId" for the 3rd select. On 7/21/06, Fred Janon <[EMAIL PROTECTED]> wrote: Hi, I have 3 tables: Tasks: taskid int PK jobid int FK description String Jobs: jobid PK clientid int FK Clients: clientid PK clientname String. How can I get the Clients:clientname via the Jobs table starting from a taskid? I know how to do it with 1 level on "indirection" but I don't know how to chain 2 queries... taskid -> jobid -> clientid -> clientname. I think it should be something like that:   <result-map name="result" class="com.jobtracking.Task">     <property name="taskId" column="taskid"/>     <property name="taskNotes" column="notes"/>     <property name="taskJobId" column="jobid"/>     <property name="clientName" column="jobid" mapped-statement="getClientNameByJobId"/>   </result-map>   <mapped-statement name="getTaskList" result-map="result">     select taskid,notes,assignedid,deptid,completion,duedate,           donedate,tasktypeid,startdate,jobid     from tasks order by duedate   </mapped-statement>   <result-map name="clientNameResult" class="com.jobtracking.Job">     <property name="clientId" column="clientid"/>     <property name="clientName" column="clientid" mapped-statement="getClientNameById"/>   </result-map>   <mapped-statement name="getClientNameByJobId" result-map="clientNameResult">     select clientid from jobs where jobid=#value#   </mapped-statement>   <mapped-statement name="getClientNameById" result-class="java.lang.String">     select name from clients where clientid = #value#   </mapped-statement> Thanks Fred