Has_many and belongs_to with non-primary foreign keys

Hi,

I’m having a bit of trouble with my first Rails app.


I have two tables:
create_table :items do |t|
t.column :created_at, :timestamp
t.column :user_id, :int
t.column :text, :text
end
create_table :users do |t|
t.column :user_id, :int
t.column :name, :string
end


I’m trying to use the “user_id” field to link both tables, with each
user “has_many” items, and items “belong_to” users.


In my user.rb:
class User < ActiveRecord::Base
has_many :items, :foreign_key => ‘user_id’
end

And in my item.rb:
class Item < ActiveRecord::Base
belongs_to :user, :foreign_key => ‘user_id’
end


In my controller, I can get the effect I want by doing something like
this:
@user = User.find(:first, :conditions => “name = ‘#{params[:id]}’”)
@items = Item.find(:all, :conditions => “user_id = ‘#{@user.user_id}’”)

But I was hoping that by adding “, :include => :tweets” to the @user
call, that I would be able to automatically grab the associated items.

Sorry if this is a very basic question, but I haven’t found any
documentation all day and I’m sure I’m just missing something
conceptually.

Thanks

First of all your relationship is invalid. In relational databases
there are three valid types of relationships, which are one-to-one,
one-to-many, and many-to-many.

In Rails a one-to-one relationship is actually represented by a
validated one-to-many relationship. In other words as far as the
database is concerned it is a one-to-many. On the Rails side you use
has_one in place of the has_many. This simply limits the one-to-many
so the many side allows only one associated object.

User:
has_one :item

Item:
belongs_to :user

At one-to-many is similar to a one-to-one except it uses has_many like
this:

User:
has_many :items

Item:
belongs_to :user

The third type of relationship requires what is called a “join table”
and is called a many-to-many relationship:

User:
has_and_belongs_to_many :items

Item:
has_and_belongs_to_many :users

This requires a third table that contains two foreign keys making up
two one-to-many relationships:

create_table :items do |t|
t.column :created_at, :timestamp
t.column :text, :text
end
create_table :users do |t|
t.column :name, :string
end
create_table :items_users do |t|
t.column :item_id, :integer
t.column :user_id, :integer

You never join two table between two foreign keys. There is no way for
the database to track the relationship. All relations are based on one
of these three basic types.

Also note that Rails provide has_many :through for many-to-many
relationships where the joining table has it’s own model object. This
is used to expose additional columns inside the join table that are
used to track information related to both side of the many-to-many
association. An example of this would be Suppliers and Products. You
may want to track things like the last purchase price of a specific
product that was purchased from a specific supplier. This value would
be stored inside the join table. Then the join table would have it own
model object for accessing that information. Call it maybe
ProductSupplier. Then you could do something like
my_product_supplier.last_purchase_price, where my_product_supplier is
an instance of the class ProductSupplier.

Hope this helps.
On Apr 18, 4:27 pm, Tom F. [email protected]

Robert W. wrote:

First of all your relationship is invalid. In relational databases
there are three valid types of relationships, which are one-to-one,
one-to-many, and many-to-many.

Ok, thanks for the overview, there weren’t nearly enough basic HowTo’s
for this. I’m still unsure of what type of relationship I should have
then.

You never join two table between two foreign keys. There is no way for
the database to track the relationship. All relations are based on one
of these three basic types.

Yeah, I figured that was probably my problem. The issue is that the
user_id is already specified (as something like: ‘11384739’) and the
regular “id” begins at 1 and auto_increments.

Hope this helps.

It does, thank you.

AndyV wrote:

I think you have an extra ‘user_id’ column that’s causing some
confusion. The User table does not need it. By default, the
create_table call is going to generate a users table with a auto-
incrementing ‘id’ column. Unless you do something explicit to
override the convention (you haven’t) then Rails is going to use that
field as the primary key/id.

By convention, a table referring to another table has a foreign key
named after the class to which it refers. That means your items table
should have a user_id column to refer to the User class (as it does).

Having followed the conventions you can just use the has_many/
belongs_to macros without modification.

I added the extra user_id column in the users table because each user
already has a specified id, as does each item (and each user has_many
items). What I was attempting to do was display all of the items for a
given user by matching the “user_id” columns of each table. I’m
basically trying to configure the models so that this call [@items =
Item.find(:all, :conditions => “user_id = ‘#{@user.user_id}’”)] can be
accessed natively like @user.items.

I hope that made sense and thanks for your help so far!

I think you have an extra ‘user_id’ column that’s causing some
confusion. The User table does not need it. By default, the
create_table call is going to generate a users table with a auto-
incrementing ‘id’ column. Unless you do something explicit to
override the convention (you haven’t) then Rails is going to use that
field as the primary key/id.

By convention, a table referring to another table has a foreign key
named after the class to which it refers. That means your items table
should have a user_id column to refer to the User class (as it does).

Having followed the conventions you can just use the has_many/
belongs_to macros without modification.

On Apr 18, 4:27 pm, Tom F. [email protected]

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs