Rails string as a foreign key
<p>I have a relation between <code>User</code> and <code>Course</code> (typical enrollment data). A <code>User</code> <code>has_many</code> <code>Course</code> and vice-versa (typical <code>JOIN</code> table scenario).</p>

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

<pre><code>class User &lt; ActiveRecord::Base

    has_and_belongs_to_many :courses
end
</code></pre>

<p>and</p>

<pre><code>class Course &lt; ActiveRecord::Base

    has_and_belongs_to_many :users
end
</code></pre>

<p>and the table name that joins the two models is <code>courses_users</code>.</p>

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

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

<p>My attempt at a migration and model rework is this:</p>

<pre><code>class User &lt; ActiveRecord::Base

    has_many :enrollment
    has_many :courses, :through =&gt; :enrollment
end
</code></pre>

<p>and</p>

<pre><code>class Course &lt; ActiveRecord::Base

    has_many :enrollment
    has_many :users, :through =&gt; :enrollment
end
</code></pre>

<p>lastly</p>

<pre><code>class Enrollment &lt; ActiveRecord::Base

    belongs_to :course
    belongs_to :user
end
</code></pre>

<p>then the migration</p>

<pre><code>class ChangeUserIdJoin &lt; ActiveRecord::Migration
    def self.up
        rename_table :courses_users, :enrollments
    end

    def self.down
        rename_table :enrollments, :courses_users
    end
end
</code></pre>

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

<p>When I attempt to change the <code>user_id</code> column type to <code>string</code> in the migration:</p>

<pre><code>class ChangeUserIdJoin &lt; 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
</code></pre>

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

<pre><code>u = User.take
  User Load (0.9ms)  SELECT  "users".* FROM "users"  LIMIT 1
 =&gt; #&lt;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&gt; 
2.1.5 :002 &gt; 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]]
 =&gt; #&lt;ActiveRecord::Associations::CollectionProxy []&gt; 
2.1.5 :003 &gt; c = Course.take
  Course Load (0.7ms)  SELECT  "courses".* FROM "courses"  LIMIT 1
 =&gt; #&lt;Course id: 12754, year: 2015, semester: 0, department: 7, course: 101, section: 1, name: "SPA 101 01 - Elementary Spanish I"&gt; 
2.1.5 :004 &gt; c.users
PG::UndefinedFunction: ERROR:  operator does not exist: integer = character varying
LINE 1: ... "users" INNER JOIN "enrollments" ON "users"."id" = "enrollm...
</code></pre>

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