How to use Joins followed by left outer join in ruby on rails?

Question

I have the following database schema structure:

School
has_many :students

Students
has_many :books

Books
(has an attribute pages(integer))

The issue is I want a total of all the pages of a book a student has at the school level. But there is also a possibility that a student might not have any book.

I want to select the following rails query using ruby code and methods like joins and includes:

SELECT * FROM school INNER JOIN students ON students.school_id = schools.id LEFT OUTER JOIN books ON books.student_id = students.id

I have tried the following:

School.joins({:students => :books}).all

But this will not take into account those students who do not have any books. How to solve this?

Answer 1

Unfortunately, your ancient version of Ruby on Rails doesn't support left_outer_joins. But you can still define joins manually with the string syntax, like this:

School
  .joins(:students) # defaults to INNER JOIN
  .joins('LEFT OUTER JOIN books ON books.student_id = students.id')
  .all

Btw. you are on an out-dated and unmaintained version of Ruby on Rails. Rails 4.1.6 reached end-of-life about 7 years ago and has a couple of well-known security vulnerabilities. I highly recommend upgrading your application to an up-to-date version of Ruby on Rails.