Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (asf.osuosl.org: domain of alan@chandlerfamily.org.uk
 designates 195.188.213.9 as permitted sender)
From: Alan Chandler <alan@chandlerfamily.org.uk>
To: user-java@ibatis.apache.org
Subject: Re: Group By for multiple column primary keys
Date: Sun, 16 Oct 2005 18:55:03 +0100
User-Agent: KMail/1.8.2
References: <200510141905.51071.alan@chandlerfamily.org.uk>
 <16178eb10510160851v48475012q1e9e9241d93801d2@mail.gmail.com>
In-Reply-To: <16178eb10510160851v48475012q1e9e9241d93801d2@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-6"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200510161855.03744.alan@chandlerfamily.org.uk>

On Sunday 16 Oct 2005 16:51, Clinton Begin wrote:
> Okay....let's start this thread over.
>
> 1) You don't need to use groupBy just because you now have a composite ke=
y.
> groupBy is for resolving N+1 select issues where you have 1:M or M:M
> relationships. Since I don't see a nested resultMap attribute in either of
> your result maps, I can only assume you don't need this.
>

Actually I do - but only because its much more complicated than I made out.=
  I=20
have currently given up with the multiple key stuff, mainly because I have=
=20
decided to do it a different way.  In fact I now have it working using two=
=20
selects one after the other, into different result maps and then use java i=
n=20
my application to merge them together.  Let me explain:-

I am building a family tree application, and my database has just two entit=
ies

Persons - key is id, foreign keys are mother and father (nulls if undefined)

Marriages - key is {husband, wife, m_no} husband and wife are foreign keys =
on=20
Persons - nulls not allowed, m_no is incremented for couples who divorce an=
d=20
re-marry. [This addition of m_no was crucial - I had it all working before =
I=20
realised I needed it]

for a given individual I want to list

a) All the spouses related to this individual by marriage (with of marriage=
=20
and divorce listed) and with the children the children from this partnershi=
p=20
listed underneath
b) All the partners for which this individual is the other parent of a chil=
d=20
but where they have not been married - the children should be listed=20
underneath
c) All the children for which there this individual is a parent

Provided I had different sql for a Male and a Female, I had managed  to=20
construct a single SQL statement that joined persons (twice - once for=20
spouse/parent and one for child) with marriages to generate this list=20

=46rom a Java classes point of view - when I first asked the question I had=
 just=20
realised the m_no problem and was trying to graft it on to my existing=20
structure.  Here I had three classes.

Person (Integer id, ...and other details such as forename and surname ... )
Marriage (Person spouse, ... and marriage details)
=46amily (Marriage marriage, List<Person> children).

A queryForList would then populate a List<Family> variable.


> 2) Using composite column definitions is for passing multiple parameters =
to
> a sub-select, which you also don't appear to be using because there's no
> select attribute in your result map either.
>
> So let's first clarify what exactly it is you're trying to do. Here's what
> we understand:
>
> * You have two columns: sid and m_no
>
> Here's the part we don't know:
>
> * What are you trying to map them to?

The previous version had worked with a Result Map to describe the family cl=
ass=20
and groupBy of "marriage.spouse.id"  (Marriage was null in the case where a=
=20
right join had left the spouse/married columns with null)

Assuming I added a marriage.m_no in, I was now wanting to=20
groupBy=3D"{marriage.spouse.id, marriage.mno}"  so that a new Family item w=
as=20
made when the combination of marriage.spouse.id and m.no varied - but when=
=20
this combination stayed the same, new Person entries would be added to the=
=20
children List)=20


>
> From your description it's unclear if you're trying to:
>
> * Map two columns to two properties (use two properties and normal
> mappings) * Map two columns to one property (use SQL concatenation and an
> alias) * Map two columns to a complex property using a object graph
> navigation (use two result mappings and object.dot.notation)
Already doing this - understand it fine=20
> * Map two columns to a complex property using a sub-select (use composite
> column mapping and the select attribute mapped to a second SQL statement)
Trying to avoid this
> * Map two columns to a complex collection using a join and repeating grou=
ps
> (use groupBy and a nested resultMap attribute to map to a second resultMa=
p)

This last one is the main one - but see the point above

However - in struggling with this, I realised that my Java classes are wron=
g=20
to map this new combination - so I redefined them as follows

Person (Integer id, ...)
Marriage (irrelevent - just mapping some attributes to this)
Relationship (Person spouse, List<Marriage> marriages, List<Person> childre=
n)

I was then hoping something like this would work

	<resultMap id=3D"marriage-list" class=3D"relationship" groupBy=3D"spouse.i=
d" >
		<result property=3D"spouse.id" column=3D"sid"/>
=2E..
		<result property=3D"marriages" resultMap=3D"Family.marriages" />
		<result property=3D"children" resultMap=3D"Family.children" />
	</resultMap>

WIth the two other result maps just mapping the Java attributes to the=20
appropriate columns from the select.  However, I now realised the underlyin=
g=20
SQL was giving problems - because on a normal relationship with multiple=20
children, I was getting repeated marriages (with the same dates).  My SQL i=
s=20
not up to fixing this (I think I want some form of union but ...)


I now do this

	<resultMap id=3D"marriage-list" class=3D"relationship" groupBy=3D"spouse.i=
d" >
		<result property=3D"spouse.id" column=3D"sid"/>
		<result property=3D"spouse.forename" column=3D"sfname" />
		<result property=3D"spouse.surname" column=3D"ssname" />
		<result property=3D"marriages" resultMap=3D"Family.marriages" />
	</resultMap>
 and=20

	<resultMap id=3D"children-list" class=3D"relationship" groupBy=3D"spouse.i=
d" >
		<result property=3D"spouse.id" column=3D"sid"/>
		<result property=3D"spouse.forename" column=3D"sfname" />
		<result property=3D"spouse.surname" column=3D"ssname" />
		<result property=3D"children" resultMap=3D"Family.children" />
	</resultMap>


and then in the Java do

List<Relationship> marriages;
Map family;

family =3D  map.queryForMap("getMaleChildren",getPersonId(),"spouse.id");
marriages =3D map.queryForList("getMaleMarriages", getPersonId());

// Now run through the marriages list and add related records to the Family
//map
for (Relationship r : marriages ) {
	Integer spouseid =3D r.getSpouse().getId();
	Relationship c =3D  (Relationship) family.get(spouseid);

	if (c =3D=3D null ) {
		family.put(spouseid,r);
	} else {
		c.setMarriages(r.getMarriages());
		family.put(spouseid,c);
	}
}


This seems to do the job

BUT I would be interested if there was a better "IBATIS" way to achieve the=
=20
same.

=2D-=20
Alan Chandler
http://www.chandlerfamily.org.uk
Open Source. It's the difference between trust and antitrust.