Holmes Holmes - 3 months ago 11
Ruby Question

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 !

dkp dkp
Answer

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
Comments