commonsensei commonsensei - 1 month ago 10
Ruby Question

Referencing a table twice in rails/postgres

I am using PostgreSQL as my db in rails. I have a User model and a Product model. I am trying to make a Transaction model where I want to reference user twice, once as buyer and once as seller. I generated the models as suggested in this post (answer by toasterlovin)
Write a migration with reference to a model twice
However it gives me an error when I use PostgreSQL (with SQLite it worked fine). I get the following error. What can I do to resolve this?


{StandardError: An error has occurred, this and all later migrations canceled:

PG::UndefinedTable: ERROR: relation "buyers" does not exist

: ALTER TABLE "transactions" ADD CONSTRAINT "fk_rails_0b24a7fcc3"

FOREIGN KEY ("buyer_id")

REFERENCES "buyers" ("id")

C:/Users/Powerhouse/Desktop/CodingDojo/ruby/rails/demo/db/migrate/20161024032156_create_transactions.rb:3:in
change'

C:in
migrate'

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: relation "buyers" does not exist

: ALTER TABLE "transactions" ADD CONSTRAINT "fk_rails_0b24a7fcc3"

FOREIGN KEY ("buyer_id")

REFERENCES "buyers" ("id")

C:/Users/Powerhouse/Desktop/CodingDojo/ruby/rails/demo/db/migrate/20161024032156_create_transactions.rb:3:in
change'

C:in
migrate'

PG::UndefinedTable: ERROR: relation "buyers" does not exist

C:/Users/Powerhouse/Desktop/CodingDojo/ruby/rails/demo/db/migrate/20161024032156_create_transactions.rb:3:in
change'

C:in
migrate'

Tasks: TOP => db:migrate

(See full trace by running task with --trace) }


Models

class User < ActiveRecord::Base
has_many :products
has_many :sales, :class_name => 'Transaction', :foreign_key => 'seller_id'
has_many :purchases, :class_name => 'Transaction', :foreign_key => 'buyer_id
end


Product Model

class Product < ActiveRecord::Base
belongs_to :user
end


Transaction model

class Transaction < ActiveRecord::Base
belongs_to :buyer, :class_name => 'User'
belongs_to :seller, :class_name => 'User'
belongs_to :product
end


Migration files

User

class CreateUsers < ActiveRecord::Migration
def change
create_table :users do |t|
t.string :name
t.timestamps null: false
end
end
end


Product

class CreateProducts < ActiveRecord::Migration
def change
create_table :products do |t|
t.references :user, index: true, foreign_key: true
t.string :name

t.timestamps null: false
end
end
end


Transaction

class CreateTransactions < ActiveRecord::Migration
def change
create_table :transactions do |t|
t.references :buyer, index: true, foreign_key: true
t.references :seller, index: true, foreign_key: true
t.references :product, index: true, foreign_key: true

t.timestamps null: false
end
end
end

Answer

This is due to PostgreSql not understanding how to make the custom association foreign keys.

Try changing the migration file to,

class CreateTransactions < ActiveRecord::Migration
  def change
    create_table :transactions do |t|
      t.integer :buyer_id
      t.integer :seller_id
      t.integer :product_id

      t.timestamps null: false
    end
    add_index(:transactions, :buyer_id)
    add_index(:transactions, :seller_id)
    add_index(:transactions, :product_id)
    add_foreign_key :transactions, :users, column: :buyer_id
    add_foreign_key :transactions, :users, column: :seller_id
  end
end

It automatically links buyer_id to user, taking the power of

belongs_to :buyer, :class_name => 'User'

The above line makes the buyer_id as foreign key.

You can also try,

class CreateTransactions < ActiveRecord::Migration
  def change
    create_table :transactions do |t|
      t.references :buyer, index: true
      t.references :seller, index: true
      t.references :product, index: true
      t.timestamps null: false
    end
    add_foreign_key :transactions, :users, column: :buyer_id
    add_foreign_key :transactions, :users, column: :seller_id
  end
end
Comments