ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Fred Janon <fja...@yahoo.com>
Subject Re: Mapped statement with chained queries ?
Date Sat, 22 Jul 2006 02:32:39 GMT
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* <fjanon@yahoo.com <mailto:fjanon@yahoo.com>>

>> 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
>>
>>
>>
>>
>

Mime
View raw message