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* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>
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.
