Linking two tables using a lookup table


#1

Hi,

How do I associate two different tables which have a lookup id (table)
as common.

I tried the has_many :table2, :through => look_up_table in my table1
model.

Any hints?

Thanks, Hari


#2

some more info on the request:

Data model for the request…

Product table (table1)

  • id
  • name
  • description
  • price
  • store_id

Store table (lookup table)

  • id
  • name
  • location

Invoice table (table2)

  • id
  • store_id
  • number
  • amount

The product & invoice tables are using a lookup table i.e. (store table)

In this scenario how can I get the all the invoices for a particular
product using the store_id?
e.g to get the first invoice amount…> product.invoice[0].amount

When I use the “has_many…through” the foreign key referenced is wrong.
How should I define the association?

Thanks, Hari


#3

You should look at has_many :through again carefully. I would look at
Josh S.'s excellent article on this topic for more information.

http://blog.hasmanythrough.com/articles/2006/04/20/many-to-many-dance-off

You’re mising some crucial fields in your linking table.


#4

Brian H. wrote:

You should look at has_many :through again carefully. I would look at
Josh S.'s excellent article on this topic for more information.

http://blog.hasmanythrough.com/articles/2006/04/20/many-to-many-dance-off

You’re mising some crucial fields in your linking table.

Hi Brian, you mean I should treat the lookup table as a link table and
add some keys?

I will go through the article, but I thought the model is fairly a
simple one and a common practice to use a lookup table. Is there any
other way to solve the problem?

Thanks, Hari


#5

has_many :through is for treating a lookup table as a linking table.

After reading your question again, here’s what you’re actually looking
for.

The products table has a relationship to the store table via the
foreign key store_id

This means

class Product < ActiveRecord::Base
belongs_to :store
end

The same is true for Invoice

class Invoice < ActiveRecord::Base
belongs_to :store
end

This allows you to do
@product = Product.find(1)
@product.store.name

Now, say you have a store, and you want all of the products:

You need to define the has_many associations on the store

class Store < ActiveRecord::Base
has_many :products
has_many :invoices
end

So now you can reference these.

@store = Store.find(1)
@products = @store.products

for product in @products
product.name
end

belongs_to always goes with the foreign key.
If your product has a store_id column, the product belongs_to store
If your store has a product_id column, the store belongs_to product (bad
idea!!)

Don’t get this confused with has_one. It’s rare that you would use
has_one.
Become familiar with associations by reading the API or the Agile book.


#6

oh, and sorry for the confusion! My mistake!


#7

On Jun 6, 2006, at 6:02 AM, Nara H. wrote:

In this scenario how can I get the all the invoices for a particular
product using the store_id?

With your model, you cannot tell which products are
in which invoices, so you cannot do what you want to
do.


– Tom M.