ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gregg D Bolinger <gthou...@gmail.com>
Subject Re: n+1 relation table
Date Sat, 21 Jan 2006 00:33:23 GMT
Thanks for the help. I really don't think this can be performed with a
single query.  Let me see if I can explain better and with a simpler
example...

Type.java
public class Type
{
   private Long type_id;
   private String type;
   //getters and setters
}

Item.java
{
   private Long item_id;
   private String title;
   private String description;
   private String imageLoc;
   private List<Type> types;
   //getters and setters
}

DB Tables

item
----------
item_id
title
description
image_loc

type
---------
type_id
type

item_type
-------------
item_id
type_id

Now the data in item_type might look something like
1 - 1
1 - 2
1 - 3

So item_id 1 would have types 1,2,and 3.

I can populate Item and I can populate Type.  What I can't seem to figure
out is how to get the List of types populated for my Item object.

Thanks for any suggestions.

Gregg
On 1/18/06, Albert L. Sapp <asapp@uiuc.edu> wrote:
>
> 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