ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alan Chandler <a...@chandlerfamily.org.uk>
Subject Re: Group By for multiple column primary keys
Date Sun, 16 Oct 2005 17:55:03 GMT
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 key.
> 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 
have currently given up with the multiple key stuff, mainly because I have 
decided to do it a different way.  In fact I now have it working using two 
selects one after the other, into different result maps and then use java in 
my application to merge them together.  Let me explain:-

I am building a family tree application, and my database has just two entities

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 
Persons - nulls not allowed, m_no is incremented for couples who divorce and 
re-marry. [This addition of m_no was crucial - I had it all working before I 
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 
and divorce listed) and with the children the children from this partnership 
listed underneath
b) All the partners for which this individual is the other parent of a child 
but where they have not been married - the children should be listed 
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 
construct a single SQL statement that joined persons (twice - once for 
spouse/parent and one for child) with marriages to generate this list 

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

Person (Integer id, ...and other details such as forename and surname ... )
Marriage (Person spouse, ... and marriage details)
Family (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 class 
and groupBy of "marriage.spouse.id"  (Marriage was null in the case where a 
right join had left the spouse/married columns with null)

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

> 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 
> * 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 groups
> (use groupBy and a nested resultMap attribute to map to a second resultMap)

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

However - in struggling with this, I realised that my Java classes are wrong 
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> children)

I was then hoping something like this would work

	<resultMap id="marriage-list" class="relationship" groupBy="spouse.id" >
		<result property="spouse.id" column="sid"/>
		<result property="marriages" resultMap="Family.marriages" />
		<result property="children" resultMap="Family.children" />

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

I now do this

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

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

and then in the Java do

List<Relationship> marriages;
Map family;

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

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

	if (c == null ) {
	} else {

This seems to do the job

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

Alan Chandler
Open Source. It's the difference between trust and antitrust.

View raw message