ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Albert L. Sapp" <as...@uiuc.edu>
Subject Re: n+1 relation table
Date Wed, 18 Jan 2006 22:08:35 GMT
Gregg D Bolinger wrote:

> You are correct.  There is no need for that column in the item table.  
> That relation exists in the items_types table.  My mistake.  The 
> problem still remains though.
>
> Gregg
>
> On 1/18/06, *Albert L. Sapp* <asapp@uiuc.edu <mailto:asapp@uiuc.edu>> 
> wrote:
>
>     Gregg D Bolinger wrote:
>
>     > I've search the archives and have found a few similar issues but
>     > nothing conclusive to what I am doing.  I can only assume no one
>     else
>     > has this problem because they know what they are doing and I don't
>     > (most likely) or no one is simply doing things the way I am doing
>     > them.  At any rate...
>     >
>     > I have a table called items and this table contains a fk to a
>     type_id
>     > from a table called type.  I then have a 3rd table called
>     items_types
>     > which contains an item_id and a type_id.
>     >
>     > Got that?  So in my Item class I will need to get a List<Type> of
>     > types.  I know how to do the n+1 solutions but  by adding in this
>     > third table I am at a loss on how to map it.  If anyone could
>     point me
>     > into the right direction, I would appreciate it.  A link to an
>     > archived question, a page in the user docs, etc.
>     >
>     > Thanks a bunch.
>     >
>     > Gregg
>
>     Gregg,
>
>     Is there a reason why you need the type_id as a foreign key in the
>     item
>     table?  You have a relationship defined between and iten and its
>     type in
>     the third table.  If you are worried about uniqueness, simply
>     define the
>     combinations in the third table as needing to be unique.  We do a
>     similar thing in many of the modules in our application.  It requires
>     additional reads to get all the information together and this may
>     not be
>     the best design, but it works for us.
>
>     This probably did not directly answer your question, just curious
>     as to
>     why you had that foreign key defined.
>
>     Respects,
>
>     Al
>
>
Ok, let me see if I understand what you want to do.  You want to get all 
the items that are of a certain type, correct?  The solution in the 
documentation is to use a join in the select and map all the result 
fields together.

Say we want the following:

<resultMap id="get-item-result" class="whatever">
    <result property="item.id" column="ITEMS.ITEM_ID"/>
    <result property="item.description" column="ITEMS.DESCRIPTION"/>
    <result property="type.id" column="TYPES.TYPE_ID"/>
    <result property="type.description" column="TYPES.DESCRIPTION"/>
</resultMap>

and we have this select:

<statement id="getItemOfType" parameterClass="int" 
resultMap="get-item-result">
    select * from ITEMS, ITEM_TYPES, TYPES
       where ITEMS.ITEM_ID=ITEM_TYPES.ITEM_ID
       and ITEM_TYPES.TYPE_ID=TYPES.TYPE_ID
       and TYPES.TYPE_ID=#value#
</statement>

Would this work?  I have never done any joins before as I am still 
learning things as I go along.  Can you do more than one join in a 
select?  I don't know if I have the idea right or not.  If I do, I may 
rethink some of my own queries.

Hope I am not totally off base on this.


Mime
View raw message