How to prevent 'Unknown column' error with rails has_many through association

Question

Attempting to refactor a has_and_belongs_to_many association in rails 4 in order to add extra columns but am struggling over a self-referential has_many_through.

An item can consist of many components. Components are really just Items and can therefore have components of their own;

class Item < ActiveRecord::Base
  has_many :components, through: :component_items
  has_many :component_items
end

class Component < ActiveRecord::Base
  has_many :items, through: :component_items
  has_many :component_items
  self.table_name ="Items"
end

class ComponentItem < ActiveRecord::Base
  has_many :items
  has_many :components
end

And the schema looks like...

create_table "component_items", force: :cascade do |t|
  t.integer "item_id",      limit: 4, null: false
  t.integer "component_id", limit: 4, null: false
end

create_table "items", force: :cascade do |t|
  t.string   "sku",    limit: 255
  t.decimal  "height", precision: 6, scale: 2
  t.decimal  "width",  precision: 6, scale: 2
  t.decimal  "depth",  precision: 6, scale: 2
end

Then

i=Item.first
i.components

I get:

ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'items.component_item_id' in 'on clause': SELECT items.* FROM items INNER JOIN component_items ON items.component_item_id = component_items.id WHERE component_items.item_id = 87675

I don't need a column 'items.component_item_id', so where have I mistakenly defined it? Then, how do I get this association to work?

Малъ Скрылевъ Малъ Скрылевъ · Accepted Answer · 2016-05-31T20:21:08

Your error is appeared because rails searches for column component_item_id in table items, but you have no such column, I believe you should create the belongs_to relation in item to it, BUT it seems you have just wrong realtions between schema and models, so for the current scheme you defined I propose models:

class Item < ActiveRecord::Base
  has_many :components, through: :component_items
  has_many :component_items
end

class Component < Item
  has_many :items, through: :component_items
  has_many :component_items
end

class ComponentItem < ActiveRecord::Base
  belongs_to :item
  belongs_to :component
end

NOTE this requires an additional string field named type in items table.

How to prevent 'Unknown column' error with rails has_many through association

1 Answers