ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Larry Meadors" <lmead...@apache.org>
Subject Re: N+1 select problem over three tables.
Date Mon, 15 Jan 2007 02:35:21 GMT
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 <ben@e2-media.co.nz> 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.
>

Mime
View raw message