Rails string as a foreign key

Question

I have a relation between User and Course (typical enrollment data). A User has_many Course and vice-versa (typical JOIN table scenario).

I am attempting to migrate my previous has_and_belongs_to_many relationship between these two models to a has_many :through relationship. My files currently look like:

class User < ActiveRecord::Base

    has_and_belongs_to_many :courses
end

and

class Course < ActiveRecord::Base

    has_and_belongs_to_many :users
end

and the table name that joins the two models is courses_users.

I now need to migrate this relationship to the has_many :through association, and also make the column type of user_id a string, as I want to use the g_number (string) attribute of User as the foreign key. Note: I don't care about the performance difference between int and varchar/string.

The short and simple problem is that I need users.g_number to reference enrollments.user_id as a foreign key, and both are strings.

My attempt at a migration and model rework is this:

class User < ActiveRecord::Base

    has_many :enrollment
    has_many :courses, :through => :enrollment
end

and

class Course < ActiveRecord::Base

    has_many :enrollment
    has_many :users, :through => :enrollment
end

lastly

class Enrollment < ActiveRecord::Base

    belongs_to :course
    belongs_to :user
end

then the migration

class ChangeUserIdJoin < ActiveRecord::Migration
    def self.up
        rename_table :courses_users, :enrollments
    end

    def self.down
        rename_table :enrollments, :courses_users
    end
end

Everything works fine here. I can do queries like User.courses and Course.users. But now I want to change the type of the user_id column in the join table to a string so that I can store the g_number (string attribute on User) and join on that instead of the serial id column of User.

When I attempt to change the user_id column type to string in the migration:

class ChangeUserIdJoin < ActiveRecord::Migration
    def self.up
        change_column :courses_users, :user_id, :string
        rename_table :courses_users, :enrollments
    end

    def self.down
        rename_table :enrollments, :courses_users
        change_column :courses_users, :user_id, :integer
    end
end

the queries Course.users and User.courses start failing (below from Rails console). User.courses returns an empty array (whereas before there are multiple Course objects), and Course.users throws an exception because of mismatched column types (which obviously makes sense):

u = User.take
  User Load (0.9ms)  SELECT  "users".* FROM "users"  LIMIT 1
 => #<User id: 1, username: "director", g_number: "g00000000", password_digest: "$2a$10$dvcOd3rHfbcR1Rn/D6VhsOokj4XiIkQbHxXLYjy5s4f...", created_at: "2016-01-06 01:36:00", updated_at: "2016-01-06 01:36:00", first_name: "Director", last_name: "", role: 0, registered: true> 
2.1.5 :002 > u.courses
  Course Load (0.9ms)  SELECT "courses".* FROM "courses" INNER JOIN "enrollments  ON "courses"."id" = "enrollments"."course_id" WHERE "enrollments"."user_id" = $1  [["user_id", 1]]
 => #<ActiveRecord::Associations::CollectionProxy []> 
2.1.5 :003 > c = Course.take
  Course Load (0.7ms)  SELECT  "courses".* FROM "courses"  LIMIT 1
 => #<Course id: 12754, year: 2015, semester: 0, department: 7, course: 101, section: 1, name: "SPA 101 01 - Elementary Spanish I"> 
2.1.5 :004 > c.users
PG::UndefinedFunction: ERROR:  operator does not exist: integer = character varying
LINE 1: ... "users" INNER JOIN "enrollments" ON "users"."id" = "enrollm...

I need to be able to join on enrollments.user_id = users.g_number. What do I need to do in order to change the user_id column to a string type in the Enrollment model/table, and still be able to do Active Record queries like User.courses and Course.users?

Answer 1

Try by specifying the foreign and primary keys in enrollments model, like this

belongs_to :user foreign_key: :user_id, primary_key: :g_number