You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Jeremy Cowgar <je...@cowgar.com> on 2008/09/26 06:21:34 UTC
Same relational link, multiple times, different names
Hello,
I have a Users table then a Claim table. The Claim table links to the
User table many times. For instance: cleric_id, doctor_id,
pricer_id ... all of those are different users performing well
defined roles with the claim. I know how to link 1 of them via a
SqlMap. I have a resultSet defined but it's field names are like:
USER_ID, USER_CODE, USER_FIRST_NAME, USER_LAST_NAME.
How can I handle mapping CLERIC_ID, CLERIC_CODE, ... DOCTOR_ID,
DOCTOR_CODE, ... in one select with out having to create 3 resultMaps ?
Thank you,
Jeremy
Re: Same relational link, multiple times, different names
Posted by Larry Meadors <la...@gmail.com>.
You'll have to either use position (bad idea) or alias the results (do that):
select
blah, blah, blah,
cleric.user_id as clericUserId, /* other cleric.fields as clericXxx */
doctor.user_id as doctorUserId, /* other pricer.fields as pricerXxx */
pricer.user_id as pricerUserId /* other pricer.fields as pricerXxx */
from claim
join user cleric on claim.cleric_id = cleric.user_id
join user doctor on claim.doctor_id = doctor.user_id
join user pricer on claim.pricer_id = pricer.user_id
where
/* whatever */
Remember, ibatis just runs your sql, then maps the results to objects
- it's doesn't know (or care) what the SQL is or the source of the
results.
Larry
On Thu, Sep 25, 2008 at 10:21 PM, Jeremy Cowgar <je...@cowgar.com> wrote:
> Hello,
>
> I have a Users table then a Claim table. The Claim table links to the User
> table many times. For instance: cleric_id, doctor_id, pricer_id ... all of
> those are different users performing well defined roles with the claim. I
> know how to link 1 of them via a SqlMap. I have a resultSet defined but it's
> field names are like:
>
> USER_ID, USER_CODE, USER_FIRST_NAME, USER_LAST_NAME.
>
> How can I handle mapping CLERIC_ID, CLERIC_CODE, ... DOCTOR_ID, DOCTOR_CODE,
> ... in one select with out having to create 3 resultMaps ?
>
> Thank you,
>
> Jeremy
>
>
Re: Same relational link, multiple times, different names
Posted by Jeremy Cowgar <je...@cowgar.com>.
Larry,
Yes, I know how to do this via SQL. What I do not know how to do is map
it via the XML mapping. Right now I have something like:
(in User.xml)
<resultMap id="userBasicMap" class="User">
<result property="id" column="USER_ID" />
<result property="code" column="USER_CODE" />
... rest of props ...
</resultMap>
(in Claim.xml)
<resultMap id="claimBasicResultMap" class="Claim">
<result property="blah....." column="blah" />
....
<result property="cleric?????" resultMap="userBasicMap" />
</resultMap>
<select id="claimGet" resultMap="claimBasicResultMap">
SELECT blah, blah, blah, cleric.id as CLERIC_USER_ID, cleric.code AS
CLERIC_USER_CODE, ... same for doc, pricer, ... FROM claim WHERE id=#id#
</select>
I was trying to avoid mapping the User table 3 times in
claimBasicResultMap. It seems as though I will have to map it three
times? As well as a claim relates to a statuses table twice, a provider
table twice (billing and payto) as well as the patient table twice
(actual patient, then the insured). That seems like a lot of repetitive
work. Granted, it only has to be done once but...
Jeremy
========
Larry Meadors wrote:
You'll have to either use position (bad idea) or alias the results (do that):
select
blah, blah, blah,
cleric.user_id as clericUserId, /* other cleric.fields as clericXxx */
doctor.user_id as doctorUserId, /* other pricer.fields as pricerXxx */
pricer.user_id as pricerUserId /* other pricer.fields as pricerXxx */
from claim
join user cleric on claim.cleric_id = cleric.user_id
join user doctor on claim.doctor_id = doctor.user_id
join user pricer on claim.pricer_id = pricer.user_id
where
/* whatever */
Remember, ibatis just runs your sql, then maps the results to objects
- it's doesn't know (or care) what the SQL is or the source of the
results.
Larry
Jeremy Cowgar wrote:
> Hello,
>
> I have a Users table then a Claim table. The Claim table links to the
> User table many times. For instance: cleric_id, doctor_id, pricer_id
> ... all of those are different users performing well defined roles
> with the claim. I know how to link 1 of them via a SqlMap. I have a
> resultSet defined but it's field names are like:
>
> USER_ID, USER_CODE, USER_FIRST_NAME, USER_LAST_NAME.
>
> How can I handle mapping CLERIC_ID, CLERIC_CODE, ... DOCTOR_ID,
> DOCTOR_CODE, ... in one select with out having to create 3 resultMaps ?
>
> Thank you,
>
> Jeremy
>