Return-Path: 
 <user-java-return-15328-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 59109 invoked from network); 26 Sep 2008 15:17:28 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 26 Sep 2008 15:17:28 -0000
Received: (qmail 55926 invoked by uid 500); 26 Sep 2008 15:17:26 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 55283 invoked by uid 500); 26 Sep 2008 15:17:24 -0000
Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-java-help@ibatis.apache.org>
List-Unsubscribe: <mailto:user-java-unsubscribe@ibatis.apache.org>
List-Post: <mailto:user-java@ibatis.apache.org>
List-Id: <user-java.ibatis.apache.org>
Reply-To: user-java@ibatis.apache.org
Delivered-To: mailing list user-java@ibatis.apache.org
Received: (qmail 55272 invoked by uid 99); 26 Sep 2008 15:17:24 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 26 Sep 2008 08:17:24 -0700
X-ASF-Spam-Status: No, hits=1.2 required=10.0
	tests=SPF_NEUTRAL
X-Spam-Check-By: apache.org
Received-SPF: neutral (athena.apache.org: local policy)
Received: from [207.158.37.38] (HELO merlin.advancedpricing.com)
 (207.158.37.38)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 26 Sep 2008 15:16:23 +0000
Received: from rrcs-24-172-215-187.central.biz.rr.com ([24.172.215.187]
 helo=[192.168.1.103])
	by merlin.advancedpricing.com with esmtpsa
 (TLS-1.0:DHE_RSA_AES_256_CBC_SHA1:32)
	(Exim 4.69)
	(envelope-from <jeremy@cowgar.com>)
	id 1KjF13-00050A-1U
	for user-java@ibatis.apache.org; Fri, 26 Sep 2008 11:13:57 -0400
Message-ID: <48DCFCD4.8010407@cowgar.com>
Date: Fri, 26 Sep 2008 11:16:36 -0400
From: Jeremy Cowgar <jeremy@cowgar.com>
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
To: user-java@ibatis.apache.org
Subject: Re: Same relational link, multiple times, different names
References: <C8DF2A63-8429-4095-BF4A-800F6F4BFD5E@cowgar.com>
In-Reply-To: <C8DF2A63-8429-4095-BF4A-800F6F4BFD5E@cowgar.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Spam_score: -3.0
X-Spam_score_int: -29
X-Spam_bar: ---
X-Spam_report: Spam detection software,
 running on the system "merlin.advancedpricing.com", has
	identified this incoming email as possible spam.  The original message
	has been attached to this so you can view it (if it isn't spam) or label
	similar future email.  If you have any questions, see
	the administrator of that system for details.
	Content preview:  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) [...]
	Content analysis details:   (-3.0 points, 5.0 required)
	pts rule name              description
	---- ----------------------
 --------------------------------------------------
	-1.8 ALL_TRUSTED            Passed through trusted hosts only via SMTP
	-2.6 BAYES_00               BODY: Bayesian spam probability is 0 to 1%
	[score: 0.0000]
	1.4 AWL                    AWL: From: address is in the auto white-list
X-Virus-Checked: Checked by ClamAV on apache.org

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
>