Rails 4 - Has_many through - StatementInvalid - SQLite3::SQLException: no such column:

Question

I have been strugling on this issue for 4 days and I am wondering whether I am not facing an ActiveRecord bug? I am trying to link a User model to a Callout model.

user.rb

class User < ActiveRecord::Base
    has_many :callouts_users
    has_many :callouts, through: :callouts_users    
    devise  :database_authenticatable, :registerable, :recoverable, :rememberable, :trackable, :validatable     
    has_many :posts, inverse_of: :creator
    has_many :callouts, as: :calloutable    
    has_many :profiles, as: :profileable, :validate => true     
    validates :name, presence: true
    validates_uniqueness_of :name, :case_sensitive => false, :message => "This name has already been taken"
end

callout.rb

class Callout < ActiveRecord::Base
    has_many :callouts_users
    has_many :users, through: :callouts_users
    belongs_to :conversation
    belongs_to :calloutable, polymorphic: true, class_name: "::Callout", :validate => true  
    validates :conversation, presence: true
    validates :calloutable, presence: true
    validates_uniqueness_of :calloutable_id, :scope => [:user_id, :conversation_id, :calloutable_type]
end

user_callout.rb

class UserCallout < ActiveRecord::Base
    belongs_to :user
    belongs_to :callout
    validates :type, presence: true, 
    validates :type, :inclusion=> { :in => ["up", "down"] }
end

My migrations are as follows:

..._create_callouts_users.rb

class CreateCalloutsUsers < ActiveRecord::Migration
  def change
    create_table :callouts_users do |t|
      t.timestamps null: false
    end
  end
end

..._add_callout_to_callouts_users.rb

class AddCalloutToCalloutsUsers < ActiveRecord::Migration
  def change
    add_reference :callouts_users, :callout, index: true
    add_foreign_key :callouts_users, :callouts
  end
end

..._add_user_to_callouts_users.rb

class AddUserToCalloutsUsers < ActiveRecord::Migration
  def change
    add_reference :callouts_users, :user, index: true
    add_foreign_key :callouts_users, :users
  end
end

and when I try to do something like

@callout = @conversation.callouts.find_by(calloutable: @user) 
if(@callout.nil?) @callout = Callout.new(conversation: @conversation, calloutable: @user)
@callout.users << current_user
@callout.save

I immediately have: ActiveRecord::StatementInvalid in CalloutsController#create SQLite3::SQLException: no such column: callouts.user_id: SELECT 1 AS one FROM "callouts" WHERE ("callouts"."calloutable_id" IS NULL AND "callouts"."user_id" IS NULL AND "callouts"."conversation_id" IS NULL AND "callouts"."calloutable_type" IS NULL) LIMIT 1

So as if ActiverRecords where looking for a "user_id" column on my callouts table while the user_id is only on the join table side... I am doing something wrong on my model? Why is my has_many - trough association not recogognized?

Here is the SQL code generated:

User Exists (0.2ms) SELECT 1 AS one FROM "users" WHERE (LOWER("users"."name") = LOWER('name10') AND "users"."id" != 10) LIMIT 1

Callout Exists (0.6ms) SELECT 1 AS one FROM "callouts" WHERE ("callouts"."calloutable_id" IS NULL AND "callouts"."user_id" IS NULL AND "callouts"."conversation_id" = 1 AND "callouts"."calloutable_type" IS NULL) LIMIT 1

SQLite3::SQLException: no such column: callouts.user_id: SELECT 1 AS one FROM "callouts" WHERE ("callouts"."calloutable_id" IS NULL AND "callouts"."user_id" IS NULL AND "callouts"."conversation_id" = 1 AND "callouts"."calloutable_type" IS NULL) LIMIT 1

(0.0ms) rollback transaction

Completed 500 Internal Server Error in 50ms

ActiveRecord::Schema.define(version: 20150720002524) do

  create_table "callouts", force: :cascade do |t|
    t.datetime "created_at",       null: false
    t.datetime "updated_at",       null: false
    t.integer  "conversation_id"
    t.integer  "calloutable_id"
    t.string   "calloutable_type"
  end

  add_index "callouts", ["calloutable_type", "calloutable_id"], name: "index_callouts_on_calloutable_type_and_calloutable_id"
  add_index "callouts", ["conversation_id"], name: "index_callouts_on_conversation_id"

  create_table "callouts_users", force: :cascade do |t|
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.integer  "user_id"
    t.integer  "callout_id"
  end

  add_index "callouts_users", ["callout_id"], name: "index_callouts_users_on_callout_id"
  add_index "callouts_users", ["user_id"], name: "index_callouts_users_on_user_id"

  create_table "conversations", force: :cascade do |t|
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "posts", force: :cascade do |t|
    t.datetime "created_at",      null: false
   t.datetime "updated_at",      null: false
    t.integer  "conversation_id"
    t.integer  "creator_id"
    t.text     "title"
    t.text     "content"
 end

  add_index "posts", ["conversation_id"], name: "index_posts_on_conversation_id"
  add_index "posts", ["creator_id"], name: "index_posts_on_creator_id"

  create_table "potential_users", force: :cascade do |t|
    t.datetime "created_at", null: false
   t.datetime "updated_at", null: false
  end

  create_table "profiles", force: :cascade do |t|
   t.datetime "created_at",       null: false
    t.datetime "updated_at",       null: false
    t.integer  "profileable_id"
    t.string   "profileable_type"
    t.string   "description"
 end

  add_index "profiles", ["description"], name: "index_profiles_on_description", unique: true
  add_index "profiles", ["profileable_type", "profileable_id"], name: "index_profiles_on_profileable_type_and_profileable_id"

  create_table "users", force: :cascade do |t|
    t.datetime "created_at",                          null: false
    t.datetime "updated_at",                          null: false
    t.string   "email",                  default: "", null: false
    t.string   "encrypted_password",     default: "", null: false
    t.string   "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.integer  "sign_in_count",          default: 0,  null: false
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.string   "current_sign_in_ip"
    t.string   "last_sign_in_ip"
    t.string   "name"
  end

  add_index "users", ["email"], name: "index_users_on_email", unique: true
  add_index "users", ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true

end

===================================================> I have been strugling on this issue for 4 days and I am wondering whether I am not facing an ActiveRecord bug? I am trying to link a User model to a Callout model.

user.rb

class User < ActiveRecord::Base
    has_many :callouts_users
    has_many :callouts, through: :callouts_users    
    devise  :database_authenticatable, :registerable, :recoverable, :rememberable, :trackable, :validatable     
    has_many :posts, inverse_of: :creator
    has_many :callouts, as: :calloutable    
    has_many :profiles, as: :profileable, :validate => true     
    validates :name, presence: true
    validates_uniqueness_of :name, :case_sensitive => false, :message => "This name has already been taken"
end

callout.rb

class Callout < ActiveRecord::Base
    has_many :callouts_users
    has_many :users, through: :callouts_users
    belongs_to :conversation
    belongs_to :calloutable, polymorphic: true, class_name: "::Callout", :validate => true  
    validates :conversation, presence: true
    validates :calloutable, presence: true
    validates_uniqueness_of :calloutable_id, :scope => [:user_id, :conversation_id, :calloutable_type]
end

user_callout.rb

class UserCallout < ActiveRecord::Base
    belongs_to :user
    belongs_to :callout
    validates :type, presence: true, 
    validates :type, :inclusion=> { :in => ["up", "down"] }
end

My migrations are as follows:

..._create_callouts_users.rb

class CreateCalloutsUsers < ActiveRecord::Migration
  def change
    create_table :callouts_users do |t|
      t.timestamps null: false
    end
  end
end

..._add_callout_to_callouts_users.rb

class AddCalloutToCalloutsUsers < ActiveRecord::Migration
  def change
    add_reference :callouts_users, :callout, index: true
    add_foreign_key :callouts_users, :callouts
  end
end

..._add_user_to_callouts_users.rb

class AddUserToCalloutsUsers < ActiveRecord::Migration
  def change
    add_reference :callouts_users, :user, index: true
    add_foreign_key :callouts_users, :users
  end
end

and when I try to do something like

@callout = @conversation.callouts.find_by(calloutable: @user) 
if(@callout.nil?) @callout = Callout.new(conversation: @conversation, calloutable: @user)
@callout.users << current_user
@callout.save

I immediately have: ActiveRecord::StatementInvalid in CalloutsController#create SQLite3::SQLException: no such column: callouts.user_id: SELECT 1 AS one FROM "callouts" WHERE ("callouts"."calloutable_id" IS NULL AND "callouts"."user_id" IS NULL AND "callouts"."conversation_id" IS NULL AND "callouts"."calloutable_type" IS NULL) LIMIT 1

So as if ActiverRecords where looking for a "user_id" column on my callouts table while the user_id is only on the join table side... I am doing something wrong on my model? Why is my has_many - trough association not recogognized?

Here is the SQL code generated:

User Exists (0.2ms) SELECT 1 AS one FROM "users" WHERE (LOWER("users"."name") = LOWER('name10') AND "users"."id" != 10) LIMIT 1

Callout Exists (0.6ms) SELECT 1 AS one FROM "callouts" WHERE ("callouts"."calloutable_id" IS NULL AND "callouts"."user_id" IS NULL AND "callouts"."conversation_id" = 1 AND "callouts"."calloutable_type" IS NULL) LIMIT 1

SQLite3::SQLException: no such column: callouts.user_id: SELECT 1 AS one FROM "callouts" WHERE ("callouts"."calloutable_id" IS NULL AND "callouts"."user_id" IS NULL AND "callouts"."conversation_id" = 1 AND "callouts"."calloutable_type" IS NULL) LIMIT 1

(0.0ms) rollback transaction

Completed 500 Internal Server Error in 50ms

ActiveRecord::Schema.define(version: 20150720002524) do

  create_table "callouts", force: :cascade do |t|
    t.datetime "created_at",       null: false
    t.datetime "updated_at",       null: false
    t.integer  "conversation_id"
    t.integer  "calloutable_id"
    t.string   "calloutable_type"
  end

  add_index "callouts", ["calloutable_type", "calloutable_id"], name: "index_callouts_on_calloutable_type_and_calloutable_id"
  add_index "callouts", ["conversation_id"], name: "index_callouts_on_conversation_id"

  create_table "callouts_users", force: :cascade do |t|
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.integer  "user_id"
    t.integer  "callout_id"
  end

  add_index "callouts_users", ["callout_id"], name: "index_callouts_users_on_callout_id"
  add_index "callouts_users", ["user_id"], name: "index_callouts_users_on_user_id"

  create_table "conversations", force: :cascade do |t|
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "posts", force: :cascade do |t|
    t.datetime "created_at",      null: false
   t.datetime "updated_at",      null: false
    t.integer  "conversation_id"
    t.integer  "creator_id"
    t.text     "title"
    t.text     "content"
 end

  add_index "posts", ["conversation_id"], name: "index_posts_on_conversation_id"
  add_index "posts", ["creator_id"], name: "index_posts_on_creator_id"

  create_table "potential_users", force: :cascade do |t|
    t.datetime "created_at", null: false
   t.datetime "updated_at", null: false
  end

  create_table "profiles", force: :cascade do |t|
   t.datetime "created_at",       null: false
    t.datetime "updated_at",       null: false
    t.integer  "profileable_id"
    t.string   "profileable_type"
    t.string   "description"
 end

  add_index "profiles", ["description"], name: "index_profiles_on_description", unique: true
  add_index "profiles", ["profileable_type", "profileable_id"], name: "index_profiles_on_profileable_type_and_profileable_id"

  create_table "users", force: :cascade do |t|
    t.datetime "created_at",                          null: false
    t.datetime "updated_at",                          null: false
    t.string   "email",                  default: "", null: false
    t.string   "encrypted_password",     default: "", null: false
    t.string   "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.integer  "sign_in_count",          default: 0,  null: false
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.string   "current_sign_in_ip"
    t.string   "last_sign_in_ip"
    t.string   "name"
  end

  add_index "users", ["email"], name: "index_users_on_email", unique: true
  add_index "users", ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true

end

=============================> EDIT

Ok,

I have another clue. I think that the error lies in the User Model but I don't know how to write it.

To make short:

1.) Different UserS can participate in differents Callouts. So User <=> Callout is a "has_many / through" relation. (not HABTM because I need to customize the Join Model). So I can write @callout.users

2.) One Callout targets at one Calloutable (Calloutable are either User or PotentialUser). But a Calloutable may be targeted by different Callouts. So it is a belong_to / has_many Polymorphic relation. I can write @user.callouts...and also @callout.users...

But : @callout.users in situation 1) or 2) don't mean the same thing.

Here are the detailled models:

class Callout < ActiveRecord::Base
    has_many :callouts_users
    has_many :users, through: :callouts_users

    belongs_to :calloutable, polymorphic: true, class_name: "::Callout", :validate => true    
    validates :calloutable, presence: true
    validates_uniqueness_of :calloutable_id, :scope => [:user_id, :conversation_id, :calloutable_type]

    belongs_to :conversation
    validates :conversation, presence: true
end

class CalloutsUser < ActiveRecord::Base
    belongs_to :user
    belongs_to :callout
    validates_uniqueness_of :user_id, :scope => [:callout_id]
end

class User < ActiveRecord::Base
    has_many :callouts_users
    has_many :callouts, through: :callouts_users
    has_many :callouts, as: :calloutable

    devise :database_authenticatable, :registerable, :recoverable, :rememberable, :trackable, :validatable
    has_many :posts, inverse_of: :creator    
    has_many :profiles, as: :profileable, :validate => true 

    validates :name, presence: true
    validates_uniqueness_of :name, :case_sensitive => false, :message => "This name has already been taken"
end

class PotentialUser < ActiveRecord::Base
    has_many :callouts, as: :calloutable      
    has_one :profile, as: :profileable, :validate => true
    validates :profile, presence: true
end

Any idea to rewrite the User Model part ? (the 2 has_many :callouts) I think I just need to make difference to rails between @user.callouts received by the user and @user.callouts made by the user...

I haven't read all the way through, but it looks as though you are using user_callouts as a join table or merge table. Don't fight convention. Rails wants you to name that callouts_users. That will eliminate your requirement for a :through — David Hoelzer
What could you possibly need to add that would prevent you from defining a habtm according to the framework? The table will just be named differently. Nothing else changes or becomes unavailable. — David Hoelzer
In case I wasn't clear or you misunderstood, the reason you can remove the :through is that it becomes unnecessary if you simply name the tables properly. — David Hoelzer
Unfortunately I need the has_many / through association because I will have to add some stuffs on the callouts_users table. I know about rails naming convention, but it is only, for HBTM, isn't it ? (For HM through, one should be able to choose the model name ?) Just to be sure, I have renamed user_callouts to callouts_users but nothing changed. Rails misses the join table and keeps looking for a "user_id" on callout...And I need to access the joined model to add it other columns. — Beuun
Nothing prevents you from adding a model for a merge table with whatever conditions you want in it. — David Hoelzer

David Hoelzer David Hoelzer · Accepted Answer · 2015-07-20T00:13:24

I feel as though you've made this harder than it needs to be. Don't fight the framework, it's here to help!

First, for your join table, use the standard Rails naming convention: order the models alphabetically. If you roll back a bit, rather than create three migrations for one action, why not:

rails g migration callouts_users callout_id:integer user_id:integer

That coupled with has_and_belongs_to_many relations in your models and you should be good to go.

Rails 4 - Has_many through - StatementInvalid - SQLite3::SQLException: no such column:

user.rb

callout.rb

user_callout.rb

..._create_callouts_users.rb

..._add_callout_to_callouts_users.rb

..._add_user_to_callouts_users.rb

user.rb

callout.rb

user_callout.rb

..._create_callouts_users.rb

..._add_callout_to_callouts_users.rb

..._add_user_to_callouts_users.rb

2 Answers