why does column trades.item_id not exist?

Question

I have a relationship model in which two Users can enter into a Trade for the exchange of two Items.

class User < ActiveRecord::Base
 has_many :owned_items, class_name: "Item"
 has_many :trades_received, class_name: "Trade", through: :owned_items, source: :trades
 has_many :trades
 has_many :wanted_items, class_name: "Item", through: :trades, source: :item
end

class Item < ActiveRecord::Base
 belongs_to :owner, class_name: "User", foreign_key: :user_id
 has_many :trades, dependent: :destroy
 has_many :trade_requesters, through: :trades
 has_many :trade_recipients, through: :trades
end

class Trade < ActiveRecord::Base
 belongs_to :trade_requester, class_name: "User"
 belongs_to :trade_recipient, class_name: "User"
 belongs_to :wanted_item, class_name: "Item", foreign_key: :wanted_item_id
 belongs_to :collateral_item, class_name: "Item", foreign_key: :collateral_item_id
end

The migration on my Trades table looks like this:

create_table :trades do |t|
 t.belongs_to :trade_requester
 t.belongs_to :trade_recipient
 t.belongs_to :wanted_item
 t.belongs_to :collateral_item
end

The stack trace leads to a helper method I'm using to list all Trade requests. That line says @trades = current_user.trades_received.requested.count, and then on down to the model association on User where has_many :owned_items, class_name: "Item". From my understanding, it looks like the trades_received method, which is called through: :owned_items and source: :trades should be referencing the :wanted_item_id foreign key in the migration. But it is not. It works if I create a migration to add item_id, but a Trade needs two items, and so I've split it up into the two wanted_item and collateral_item associations. How do I set that User association up so that it references the Item that is being requested by another User? Should Items has_many :trades, the way I have it, or should Items belongs_to :trades?

Full error:

PG::UndefinedColumn: ERROR:  column trades.item_id does not exist
LINE 1: ...LECT COUNT(*) FROM "trades" INNER JOIN "items" ON "trades"."...
                                                         ^
: SELECT COUNT(*) FROM "trades" INNER JOIN "items" ON "trades"."item_id" = "items"."id" WHERE "items"."user_id" = $1 AND "trades"."approved" IS NULL

tldr: I need to track a bunch of complex has_many :through associations, I don't think my data model is correct, and need help understanding why. Thank you.

Answer 1

You're setting up two has_many :through relationship between User and Item, with Trade as the join table for both. You got some relationship confused. Here is a setup based on your migration:

class User < ActiveRecord::Base
 has_many :received_trades, class_name: "Trade", foreign_key: "trade_recipient"
 has_many :requested_trades, class_name: "Trade", foreign_key: "trade_requester"
 has_many :collateral_items, through: :received_trades
 has_many :wanted_items, through: :requested_trades
end

class Item < ActiveRecord::Base
 has_many :collateral_items, class_name: "Trade", foreign_key: "collateral_item"
 has_many :wanted_items, class_name: "Trade", foreign_key: "wanted_item"
 has_many :trade_requesters, through: :wanted_items
 has_many :trade_recipients, through: :collateral_items
end

class Trade < ActiveRecord::Base
 belongs_to :trade_requester, class_name: "User"
 belongs_to :trade_recipient, class_name: "User"
 belongs_to :wanted_item, class_name: "Item"
 belongs_to :collateral_item, class_name: "Item"
end

##migration
create_table :trades do |t|
 t.belongs_to :trade_requester
 t.belongs_to :trade_recipient
 t.belongs_to :wanted_item
 t.belongs_to :collateral_item
end

Some explanation:

Item has_many :collateral_item  ## item_id in table collateral_items
Item has_many :collateral_item, class_name: "Trade", foreign_key: "collateral_item"
##collateral_item_id in trades table.