Same Model for Two belongs_to Associations migration

Question

How do I create a migration for a model that has two references to the same model.

I have a user model with two roles, buyer and seller, I also have a sales model so each sale should have one buyer and one seller.

I've seen this answer that would suggest my sales model should look like

class Sale < ActiveRecord::Base
  belongs_to :buyer, :class_name => 'User', :foreign_key => 'buyer_id'
  belongs_to :seller, :class_name => 'User', :foreign_key => 'seller_id'
end

but I don't know how to create the migration and get it to work...!

Which database engine do you use? – Tobias Jan 09 '16 at 19:01 — Tobias, Jan 09 '16 at 19:01

score 7 · Accepted Answer · answered Jan 09 '16 at 19:08

7

You have to create the following migration:

rails g migration AddBuyerAndSellerToSales buyer:references seller:references

This should create the following migration file:

class AddBuyerAndSellerToSales < ActiveRecord::Migration
  def change
    add_reference :sales, :buyer, index: true, foreign_key: true
    add_reference :sales, :seller, index: true, foreign_key: true
  end
end

If you use a database engine like PostgreSQL you have to tell the engine to which table the foreign key will point.

class AddBuyerAndSellerToSales < ActiveRecord::Migration
  def change
    add_reference :sales, :buyer, index: true   # foreign_key: true <= remove this!
    add_reference :sales, :seller, index: true  # foreign_key: true <= remove this!

    add_foreign_key :sales, :users, column: :buyer_id
    add_foreign_key :sales, :users, column: :seller_id
  end
end

Hope this helps!

answered Jan 09 '16 at 19:08

Tobias

4,523
2
20
40

that looks good, thanks, Will I be able to call Buyer.sales or Seller.sales? – raphael_turtle Jan 09 '16 at 19:35
No because you don't have a `Buyer` or `Seller` model. If you have these models you have to add the foreign keys for this table to the `sales` table and create then a `one-to-many` relationship. Do you have a `Buyer` or `Seller` model? – Tobias Jan 09 '16 at 19:39
no model, I can't get @user.sales to work, it gives the error ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column sales.user_id does not exist – raphael_turtle Jan 09 '16 at 19:41
Run `rails g migration AddUserToSales user:references` and migrate it. It should work then. – Tobias Jan 09 '16 at 19:44
but don't I already have those columns on the sales table as buyer&seller? – raphael_turtle Jan 09 '16 at 19:55
No you have the columns `buyer_id` and `seller_id`. If you want to use one of them you have to change your `has_many` call in your `User` model. I suppose you want to use the `seller_id` as the foreign key. Then everything you have to do is to change `has_many :sales, foreign_key: :seller_id` in your `User` model. – Tobias Jan 09 '16 at 19:59
You can use one liner for FK though `add_reference :sales, :buyer, foreign_key: { to_table: :users }` – shlajin Jul 17 '18 at 09:46

score 0 · Answer 2 · answered Jan 10 '16 at 12:49

This is called a self join, and can be created as follows:

#app/models/sale.rb
class Sale < ActiveRecord::Base
  belongs_to :buyer, class_name: 'User', foreign_key: :buyer_id
  belongs_to :seller, class_name: 'User', foreign_key: :seller_id
end

--

$ rails g migration CreateSales

#db/migrate/create_sales______________.rb
class CreateSales < ActiveRecord::Migrate
    def change
        change_table :sales do |t|
           t.references :seller
           t.references :buyer
        end
    end
end

$ rake db:migrate

Same Model for Two belongs_to Associations migration

2 Answers2

Linked