Nested joins in rails

Recently while contributing to RubyForGood, I came across a problem, requiring nested joins. I had not faced a similar situation before. And to my surprise, ActiveRecord seems to be able to handle this quite easily.

Premise

InKind is an application built to serve Community Education Partnerships. This application provides an admin interface to manage volunteers and students.

A Student is assigned an active Staff(User) through StudentStaffAssignment.

class Student
  has_many :student_staff_assignments
  has_one :active_student_staff_assignment,
          -> { where("student_staff_assignments.end_date > ?", Date.current) },
          class_name: "StudentStaffAssignment"
  has_one :staff, through: :active_student_staff_assignment
end

class StudentStaffAssignment < ApplicationRecord
  belongs_to :student
  belongs_to :staff, class_name: "User",
                     foreign_key: :staff_id,
                     inverse_of: :student_staff_assignments
end

A Student can respond to a Survey.

class SurveyResponse < ApplicationRecord
  belongs_to :student
  belongs_to :survey
end

There can be SupportTickets created, based on SurveyResponse provided by the student.

class SupportTicket < ApplicationRecord
  belongs_to :survey_response,
             foreign_key: :survey_response_id,
             optional: true
end

Problem and “the solution”

If we need the name of active staff assigned to the student for a support_ticket we can get it pretty easily,

ticket = SupportTicket.find(params[:id])
ticket.survey_response&.student&.active_student_staff_assignment&.staff&.name

It becomes tricky, when we need to sort all the tickets in the active staff’s name.

First, we will need to get all the data, which will need nested joins.

SupportTicket
  .left_outer_joins(
  :requestor,
  survey_response: {
    student: {
      active_student_staff_assignment: :staff
    }
  })

If you notice, we are not just doing nested joins. We are also using scopes and table alias. And ActiveRecord just works^TM

It is pretty neat!

Next up, for sorting. We want to sort on staff.last_name. We can print the SQL query using .to_sql and study the table structures.

SELECT "support_tickets".* FROM "support_tickets"
LEFT OUTER JOIN "users" ON "users"."id" = "support_tickets"."requestor_id"
LEFT OUTER JOIN "survey_responses" ON "survey_responses"."id" = "support_tickets"."survey_response_id"
LEFT OUTER JOIN "students" ON "students"."id" = "survey_responses"."student_id"
LEFT OUTER JOIN "student_staff_assignments" ON "student_staff_assignments"."student_id" = "students"."id"
                AND (student_staff_assignments.end_date > '2022-01-03')
LEFT OUTER JOIN "users" "staffs_student_staff_assignments" ON "staffs_student_staff_assignments"."id" = "student_staff_assignments"."staff_id"

From the above, we can see that the staffs_student_staff_assignments table has the active staff information. And we can sort using,

SupportTicket
  .left_outer_joins(
  :requestor,
  survey_response: {
    student: {
      active_student_staff_assignment: :staff
    }
  })
  .order('staffs_student_staff_assignments.last_name' => :desc)

Which generates the query,

SELECT "support_tickets".* FROM "support_tickets"
LEFT OUTER JOIN "users" ON "users"."id" = "support_tickets"."requestor_id"
LEFT OUTER JOIN "survey_responses" ON "survey_responses"."id" = "support_tickets"."survey_response_id"
LEFT OUTER JOIN "students" ON "students"."id" = "survey_responses"."student_id"
LEFT OUTER JOIN "student_staff_assignments" ON "student_staff_assignments"."student_id" = "students"."id"
                AND (student_staff_assignments.end_date > '2022-01-03')
LEFT OUTER JOIN "users" "staffs_student_staff_assignments" ON "staffs_student_staff_assignments"."id" = "student_staff_assignments"."staff_id"
ORDER BY "staffs_student_staff_assignments"."last_name" DESC

Alright, that is all I had to share today.

Until next week!