Building an active record query with multiple joins

Question

I am trying to understand ActiveRecord Querying. coming from a sql background it's a little confusing. I am trying to write a query to get all courses a particular student is enrolled in with the following query:

def self.courses_enrolled_in(student_id)
    Course.joins(:sections).where(sections: {enrollment: Enrollment.where(student: Student.find_by(id: student_id))})
  end

but the rails console gives the following error:

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: sections.enrollment:

this was pieced together from a previously asked question, working from the inside out. I understand what the error is saying, but I guess im unclear on how to chain joins together like in sql to join one table to another. how would I write a query to pull all courses enrolled by a particular student?

models:

enrollment

class Enrollment < ApplicationRecord
  belongs_to :section
  belongs_to :student
end

Student:

class Student < ApplicationRecord
  has_many :enrollments
end

Professor:

class Section < ApplicationRecord
  has_many :enrollments
  belongs_to :professor
  belongs_to :course

end

Course:

class Course < ApplicationRecord

  belongs_to :department
  has_many :sections
  has_many :professors, through: :sections
end

Answer 1

So what it looks like you are asking is, given that a student is enrolled in many sections of many courses, which courses is the student enrolled in?

We'll ignore Professors because that has nothing to do with the original question.

or put another way

Student 1:M Enrollment M:1 Section M:1 Course

or Student M:M Section. Enrollments is the joining table for the M:M relationship between Student and Section, no?

So straight away we see that Student should be:

rails g student name
class Student < ApplicationRecord
  has_many :enrollments
  has_many :sections, through: :enrollments
end

Enrollments is a joining table, so it needs the ids of student and section

rails g enrollment student_id section_id
class Enrollment < ApplicationRecord
  belongs_to :student
  belongs_to :section
end

(defining the belongs to is only necessary if you want to work an Enrollment instance and work with it's relations)

The section

rails g section name course_id
class Section < ApplicationRecord
  has_many :enrollments
  has_many :students, through: :enrollments
  belongs_to :course
end

(again, the has_manys are only necessary if you want to get the students enrolled in a section)

And the course

rails g course name
class Course < ApplicationRecord
  has_many :sections
end

And when putting this together, we want to work with an instance of Student (get all courses a particular student is enrolled in) so the self.courses_enrolled_in(student_id) statement is the wrong way to go about it. You should be able to try this all out in the rails console, so you would start by getting a student:

(timestamps omitted from console output for brevity)

[9] pry(main)> student = Student.first
  Student Load (13.8ms)  SELECT  `students`.* FROM `students` ORDER BY `students`.`id` ASC LIMIT 1
=> #<Student:0x007fc40fb88b68
 id: 1,
 name: "Fred",

And now that we have our student, ask it which sections it has:

pry(main)> sections = student.sections
  Section Load (14.1ms)  SELECT `sections`.* FROM `sections` INNER JOIN `enrollments` ON `sections`.`id` = `enrollments`.`section_id` WHERE `enrollments`.`student_id` = '1'
=> [#<Section:0x007fc40c474208
  id: 1,
  name: "1st period",
  course_id: "1",
 #<Section:0x007fc40c474028
  id: 5,
  name: "2nd period",
  course_id: "2",
 #<Section:0x007fc40c46fe60
  id: 9,
  name: "3rd period",
  course_id: "3",
]

And for each section we can get the course:

pry(main)> sections.first.course
  Course Load (17.1ms)  SELECT  `courses`.* FROM `courses` WHERE `courses`.`id` = 1 LIMIT 1
=> #<Course:0x007fc40f756a18
 id: 1,
 name: "English",

Which would run a separate query for each result in the set, not optimal, so we can do an eager load using includes:

pry(main)> student.sections.includes(:course)
      Section Load (5.9ms)  SELECT `sections`.* FROM `sections` INNER JOIN `enrollments` ON `sections`.`id` = `enrollments`.`section_id` WHERE `enrollments`.`student_id` = '1'
      Course Load (9.5ms)  SELECT `courses`.* FROM `courses` WHERE `courses`.`id` IN (1, 2, 3)
    => [#<Section:0x007fc40faa13a8
      id: 1,
      name: "1st period",
      course_id: "1",
     #<Section:0x007fc40faa11c8
      id: 5,
      name: "2nd period",
      course_id: "2",
     #<Section:0x007fc40faa0fe8
      id: 9,
      name: "3rd period",
      course_id: "3",

So now:

pry(main)> sections.first.course
=> #<Course:0x007fc40f6cc570
 id: 1,
 name: "English",

No extra query. To streamline things I would put a course_id field in the enrollments table and then add:

rails g student name
class Student < ApplicationRecord
  has_many :enrollments
  has_many :sections, through: :enrollments
  has_many :courses, through: :enrollments
end

In sum, as to how to do the join? Let ActiveRecord do the work for you, that's what it's there for. Those relationship defintions you've supplied are how you specify the joins you want.

Answer 2

When specifying conditions of a join within the where function you should specify the table name not the association name. So your example likely needs to use "enrollments". The Active Record Query Interface Rails Guide should help you with your understanding of querying with Active Record