CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecord
Holmes
Holmes

Reputation: 27

Rails 5 Postgresql has_many through association

in my app, I have 3 models here

class User < ApplicationRecord
 has_many :specialties, class_name: "Specialty",
                        foreign_key:"teacher_id",
                        dependent: :destroy
 has_many :subjects, through: :specialties, source: :subject

class Specialty < ApplicationRecord
 belongs_to :teacher, class_name: "User"
 belongs_to :subject, class_name: "Subject"

class Subject < ApplicationRecord
 has_many :teachers, through: :specialties, source: :teacher
 has_many :specialties, class_name: "Specialty",
                        foreign_key:"subject_id",
                        dependent: :destroy

In my rails console, previously when I was using SQLite I have no problems with user = User.first then user.subjects would return me all the user's subjects. However after I have changed to PostgreSQL, it returns me 

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does not exist: integer = character varying
LINE 1: ...ects" INNER JOIN "specialties" ON "subjects"."id" = "special...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "subjects".* FROM "subjects" INNER JOIN "specialties" ON "subjects"."id" = "specialties"."subject_id" WHERE "specialties"."teacher_id" = $1

Is there any way I can do to return the user.subjects ? I noticed that this problem did not occur when following rails tutorial model of following and followed. (The twitter model)

Thank you

edit, specialties is not an integer column , i shall change it and get back THanks alot !

Upvotes: 1

Views: 446

Answers (1)

dp7
dp7

Reputation: 6749

As your error already gives hint:

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Postgresql is strict about type of columns being compared.

It looks you have foreign keys subject_id , teacher_id are of string type. So when you compare primary keys of type integer with foreign keys of type string, this error appears.

You can change column type to avoid this error:

change_column :specialties, :subject_id, :integer
change_column :specialties, :teacher_id, :integer

Upvotes: 1

Related Questions