That is what I'd expect. If you look at the results of the SQL, it
becomes more apparent.
Lets say you have 3 contacts, that have 2,3, and 5 addresses. When you
do that join, you get 10 rows back - the first one 2 times, the second
one 3 times, and the third one 5 times.
Now, if those contacts have 5, 8, and 10 attributes, when you join the
previous results (2+3+5=10 rows) with the attributes, you get 84 rows
(2*5 + 3*8 + 5*10).
What you are trying to do cannot be done in a single SQL statement,
so..it can't be done in iBATIS with a single SQL statement..unless you
use a row handler. I think you could make a row handler do that.
Larry
On 1/14/07, Ben Schmidt <[EMAIL PROTECTED]> wrote:
Hi there,
I have three tables: contacts, addresses and attributes. Each contact
has a number of addresses and a number of attributes. If I just want to
get the only the addresses or only the attributes for a contact I have
no problem, but if I try to get all the addresses and all the attributes
for a contact, I end up getting either the attributes or the addresses
(whichever one is defined last in the resultmap) doubled - i.e. I will
get four address entries when there is only two. Also note that this
does not happen if there is only one matching entry in one of the
tables. Here are my sqlmaps:
<sqlMap namespace="Contact">
<resultMap id="contactSimple" class="java.util.HashMap" groupBy="id">
<result property="id" column="contact_id" />
<result property="uuid" column="contact_uuid" />
<result property="deleted" column="contact_deleted" />
<result property="login" column="contact_login" />
<result property="passwordHash" column="contact_password" />
<result property="attributes"
javaType="java.util.ArrayList" resultMap="Contact.owner_attribute" />
<result property="addresses" javaType="java.util.ArrayList"
resultMap="Address.address" />
</resultMap>
<resultMap id="owner_attribute" class="java.util.HashMap" groupBy="id">
<result property="id" column="owner_attribute_id" />
<result property="uuid" column="owner_attribute_uuid" />
<result property="value" column="owner_attribute_value" />
</resultMap>
<resultMap id="address" class="java.util.HashMap" groupBy="id">
<result property="id" column="address_id" />
<result property="uuid" column="address_uuid" />
<result property="deleted" column="address_deleted" />
<result property="name" column="address_name" />
<result property="number" column="address_number" />
<result property="street" column="address_street" />
<result property="suburb" column="address_suburb" />
<result property="city" column="address_city" />
<result property="country" column="address_country" />
<result property="postCode" column="address_post_code" />
<result property="contactUuid" column="address_contact_uuid" />
</resultMap>
<select id="listContactSimple" resultMap="contactSimple">
SELECT
*
FROM contact
LEFT OUTER JOIN address on address_contact_uuid =
contact.contact_uuid
LEFT OUTER JOIN owner_attribute ON contact.contact_uuid =
owner_attribute_owner_uuid
WHERE
(owner_attribute_deleted ISNULL OR owner_attribute_deleted =
false )
AND (address_deleted ISNULL OR address_deleted = false )
<dynamic>
<isNotNull prepend="AND " property="deleted">
contact.contact_deleted = '$deleted$'
</isNotNull> <isNotNull
prepend="AND " property="passwordHash">
contact.contact_password = #passwordHash#
</isNotNull> <isNotNull prepend="AND "
property="login">
contact.contact_login ILIKE '%$login$%'
</isNotNull>
<isNotNull prepend="AND " property="uuid">
contact.contact_uuid = #uuid#
</isNotNull>
</dynamic>
ORDER BY contact.contact_login ASC
</select>
</sqlMap>
Any help on where I'm going wrong would be greatly appreciated.
Thanks.
Ben.
