Re: Mapped statement with chained queries ?

Fri, 21 Jul 2006 19:33:07 -0700

Thanks Nathan, but I should have said that actually that's the other way around: a client has many jobs that has many tasks. So getting the client name for a task is a 1 to 1 mapping. I would not mind solving the problem with an outer join or a subquery but so far I haven't been able to write a suitable query and it is quite important for me since I don't duplicate any field in my schema but use foreign keys everywhere.
My goal is to list the task with the client name via the job, not much more than that: 2 levels on indirection. 


Could you give me an example for solving the problem in one SQL query 
using outer join or anything else?


Thanks,

Fred

Nathan Maves wrote:

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] <mailto:[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







begin:vcard
fn:fjanon
n:Janon;Fred
email;internet:[EMAIL PROTECTED]
title:Sr S/W Engineer & Architect
x-mozilla-html:TRUE
url:http://www.geocities.com/fjanon
version:2.1
end:vcard























					Reply via email to