Rails/ActiveRecord: Eager loading join table entries via a composite primary key

Question

I'm having issues eager loading a join table with ActiveRecord in Rails (4.2.4) (w/MySQL). The issue is that the join table makes use of a composite primary key, which is causing some weird? behaviour with the eager load. Here's an example to clarify:

class Student < ActiveRecord::Base
  has_many :course_student_joins
  has_many :courses, through: :course_student_joins
end

class Course < ActiveRecord::Base
  has_many :course_student_joins
  has_many :students, through: :course_student_joins
end

class CourseStudentJoin < ActiveRecord::Base
  belongs_to :course
  belongs_to :student
end

Table: courses

id  name
1   Course 1
2   Course 2

Table: students

id  name
1   Joey
2   Johnny
3   Dee Dee

Table: course_student_joins

course_id   student_id  extra_id
    1           1           5
    2           2           6

So course_student_joins is a classic many to many join table. Here's the interesting bit, if the PRIMARY KEY on the course_student_joins table consists of just course_id (single field), this works:

> Course.eager_load(:course_student_joins).first.course_student_joins

Result:

  SQL (0.2ms)  SELECT `courses`.`id` AS t0_r0, `courses`.`name` AS t0_r1, `course_student_joins`.`course_id` AS t1_r0, `course_student_joins`.`student_id` AS t1_r1, `course_student_joins`.`extra_id` AS t1_r2 FROM `courses` LEFT OUTER JOIN `course_student_joins` ON `course_student_joins`.`course_id` = `courses`.`id` WHERE `courses`.`id` = 1 AND `courses`.`id` IN (1)
 => #<ActiveRecord::Associations::CollectionProxy [#<CourseStudentJoin course_id: 1, student_id: 1, extra_id: 5>]>

If the PRIMARY KEY is changed (database side) to a composite key of (course_id, student_id), which is what we want (as a course can't have the same student twice and the primary key needs to be unique), this is what we get:

> Course.eager_load(:course_student_joins).find_by(id:1).course_student_joins

Result:

  SQL (0.2ms)  SELECT `courses`.`id` AS t0_r0, `courses`.`name` AS t0_r1, `course_student_joins`.`course_id` AS t1_r0, `course_student_joins`.`student_id` AS t1_r1, `course_student_joins`.`extra_id` AS t1_r2 FROM `courses` LEFT OUTER JOIN `course_student_joins` ON `course_student_joins`.`course_id` = `courses`.`id` WHERE `courses`.`id` = 1 AND `courses`.`id` IN (1)
 => #<ActiveRecord::Associations::CollectionProxy []>

Note the empty collection in the second result, and the SQL that remains exactly the same in both (expected).

We've played with foreign_key, primary_key settings on the has_many declarations, and even a 'solution' that changes the loading to reflect how it was performed in rails 2.0 and below. Also played with includes/references as well. No luck, any/all help appreciated.

The end goal is to come up with an ActiveRecord collection of courses with eagerly joined students and the entries in the join table, as we need to operate off of some values in the join table within an ActiveModel::Serializer which we will be passing the collection through.

I like to call association class Enrolment instead of CourseStudentJoin. Personal preference — Dan Myasnikov
@DanielMyasnikov, I would as well, but let's not get into the nomenclature of this question. I've come up with an example to outline an issue I'm encountering in a very large project, in hopes of avoiding the need to expose a larger than necessary context. — RoLo

Benjamin Manns Benjamin Manns · Accepted Answer · 2015-10-28T21:37:27

The most Rails-friendly method would be an auto-incrementing id integer primary key + a unique index on (course_id, student_id).

Is there any reason you would want to be loading and the CourseStudentJoin records? It seems that you wouldn't want to manipulate them directly, anyways, and would be better off doing things like

Course.includes(:students).where(active: true, category: "Math").foo.bar
Student.includes(:courses).where(state: "CA").foo.bar

Rails/ActiveRecord: Eager loading join table entries via a composite primary key

3 Answers