Database field refuses to be an integer - Rails/Postgres

Question

I am creating a join table, broadly following the Railscast: http://railscasts.com/episodes/17-habtm-checkboxes-revised?view=asciicast

I cannot set the has_many records on the object and get the following error:

2.0.0p353 :012 > invoice.fly_ids
   (0.9ms)  SELECT "flies".id FROM "flies" INNER JOIN "categorizations" ON "flies"."id" = "categorizations"."fly_id" WHERE "categorizations"."invoice_id" = 1
ActiveRecord::StatementInvalid: PG::Error: ERROR:  operator does not exist: integer = character varying
LINE 1: ...ies" INNER JOIN "categorizations" ON "flies"."id" = "categor...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "flies".id FROM "flies" INNER JOIN "categorizations" ON "flies"."id" = "categorizations"."fly_id" WHERE "categorizations"."invoice_id" = 1

I can't get it to work and I think it is because the 'categorizations' table is using varchar instead of integer.

My migration file looks like:

class CreateCategorizations < ActiveRecord::Migration
  def change
    create_table :categorizations do |t|
      t.integer :user_id
      t.integer :fly_id

      t.timestamps

      add_index :categorizations, :user_id
      add_index :categorizations, :fly_id
    end
  end
end

However, when I peek at the database table that is created, :user_id and :fly_id are both varchar

Why is it creating these fields as varchar when I am specifying integer in my migration file?

(even if I get this to work, it might not fix the issue...)

Edit:

User Model:

class User < ActiveRecord::Base

....
has_many :invoices
....
end

Invoice Model:

class Invoice < ActiveRecord::Base
  attr_accessible :active
  validates :user_id, presence: true
  belongs_to :user

  has_many :categorizations
  has_many :flies, through: :categorizations
end

Invoice migration:

class CreateInvoices < ActiveRecord::Migration
  def change
    create_table :invoices do |t|
      t.boolean :active
      t.integer :user_id

      t.timestamps
    end
    add_index :invoices, :user_id
  end

end

Categorization Model:

class Categorization < ActiveRecord::Base
  attr_accessible :fly_id, :user_id

  belongs_to :invoice
  belongs_to :fly
end

Categorization migration:

class CreateCategorizations < ActiveRecord::Migration
  def change
    create_table :categorizations do |t|
      t.integer :user_id
      t.integer :fly_id

      t.timestamps

      add_index :categorizations, :user_id
      add_index :categorizations, :fly_id
    end
  end
end

Fly Model:

class Fly < ActiveRecord::Base
  attr_accessible :description, :name
  validates :description, :name, presence: true

  has_many :categorizations
  has_many :invoices, through: :categorizations
end

Fly migration:

class CreateFlies < ActiveRecord::Migration
  def change
    create_table :flies do |t|
      t.string :name
      t.string :description

      t.timestamps
    end
  end
end

score 0 · Accepted Answer · answered Feb 27 '14 at 10:53

HABTM

If you're using HABTM, your tables don't need any primary keys (id):

create_table :invoices_flies, :id => false do |t|
  t.references :user
  t.references :flies
end

has_many :through

If you're using has_many :through, your table will use a primary key, as it will be a model of its own:

#app/models/user.rb
Class User < ActiveRecord::Base
    has_many :categorizations
    has_many :flies, through: :categorizations
end

#app/models/fly.rb
Class Fly < ActiveRecord::Base
    has_many :categorizations
    has_many :users, through: :categorizations
end

#app/models/categorization.rb
Class Categorization < ActiveRecord::Base
    belongs_to :fly
    belongs_to :user
end

create_table :categorizations do |t|
  t.integer :user_id
  t.integer :fly_id
end

I think your issue will probably be with the structure of your associations - can you give us some info on how you've set them up?

Database field refuses to be an integer - Rails/Postgres

1 Answers1