many_many.rb

require 'active_record'
require 'active_support/hash_with_indifferent_access'
require 'rspec'
require 'mysql2'

DATABASE_NAME = :sql_playground_database.to_s
DATABASE_CONFIGURATION_BASE = {
  adapter: :mysql2,
  host: :localhost,
  username: ENV['MYSQL_USER_NAME'],
  password: ENV['MYSQL_USER_PASSWORD'],
}

class DummyCreator < ActiveRecord::Migration
  class << self
    def up
      create_table(:students) do |t|
        t.string :name
      end

      create_table(:classrooms) do |t|
        t.string :name
      end

      create_table(:student_classrooms) do |t|
        t.integer :student_id
        t.integer :classroom_id
      end
    rescue
      nil
    end

    def down
      drop_table(:students)
      drop_table(:classrooms)
      drop_table(:student_classrooms)
    rescue
      nil
    end
  end
end

RSpec.configure do |config|
  config.before :suite do
    ActiveRecord::Base.establish_connection(DATABASE_CONFIGURATION_BASE)
    begin
      ActiveRecord::Base.connection.create_database(DATABASE_NAME)
    rescue ActiveRecord::StatementInvalid => e
      # yay
    end

    ActiveRecord::Base.establish_connection(DATABASE_CONFIGURATION_BASE.merge(
      database: DATABASE_NAME,
    ))

    DummyCreator.up rescue nil
  end

  config.after :suite do
    DummyCreator.down
  end
end

class StudentClassroom < ActiveRecord::Base
  belongs_to :student
  belongs_to :classroom
end

class Student < ActiveRecord::Base
  has_many :student_classrooms
  has_many :classrooms, through: :student_classrooms
end

class Classroom < ActiveRecord::Base
  has_many :student_classrooms
  has_many :students, through: :student_classrooms
end


describe 'sql' do
  before :all do
    students = (0..3).map do |n|
      Student.create(name: "Student #{n}")
    end

    classes = (0..2).map do |n|
      Classroom.create(name: "Classroom #{n}")
    end

    StudentClassroom.create(student: students[0], classroom: classes[0])
    StudentClassroom.create(student: students[0], classroom: classes[1])
    StudentClassroom.create(student: students[0], classroom: classes[2])

    StudentClassroom.create(student: students[1], classroom: classes[0])
    StudentClassroom.create(student: students[1], classroom: classes[1])

    StudentClassroom.create(student: students[2], classroom: classes[1])
    StudentClassroom.create(student: students[2], classroom: classes[2])

    StudentClassroom.create(student: students[3], classroom: classes[2])

    @students = students
    @classes = classes
  end

  let(:students) { @students }
  let(:classes) { @classes }

  it 'premise' do
    expect(students[0].classrooms).to match_array([classes[0], classes[1], classes[2]])
    expect(students[1].classrooms).to match_array([classes[0], classes[1]])
    expect(students[2].classrooms).to match_array([classes[1], classes[2]])
    expect(students[3].classrooms).to match_array([classes[2]])

    expect(classes[0].students).to match_array([students[0], students[1]])
    expect(classes[1].students).to match_array([students[0], students[1], students[2]])
    expect(classes[2].students).to match_array([students[0], students[2], students[3]])
  end

  it 'students' do
    expect(
      Student
        .left_outer_joins(:classrooms)
        .select(
          :id,
          :name,
          Classroom.arel_table[:id].as('as_classroom_id'),
          Classroom.arel_table[:name].as('as_classroom_name'),
        )
        .order(
          Student.arel_table[:id].asc,
          Arel.sql('as_classroom_id').asc
        )
        .tap { |sql| p sql.to_sql }
        .as_json
        .map(&:symbolize_keys)
    ).to eq([
              { id: 1, name: 'Student 0', as_classroom_id: 1, as_classroom_name: 'Classroom 0' },
              { id: 1, name: 'Student 0', as_classroom_id: 2, as_classroom_name: 'Classroom 1' },
              { id: 1, name: 'Student 0', as_classroom_id: 3, as_classroom_name: 'Classroom 2' },

              { id: 2, name: 'Student 1', as_classroom_id: 1, as_classroom_name: 'Classroom 0' },
              { id: 2, name: 'Student 1', as_classroom_id: 2, as_classroom_name: 'Classroom 1' },

              { id: 3, name: 'Student 2', as_classroom_id: 2, as_classroom_name: 'Classroom 1' },
              { id: 3, name: 'Student 2', as_classroom_id: 3, as_classroom_name: 'Classroom 2' },

              { id: 4, name: 'Student 3', as_classroom_id: 3, as_classroom_name: 'Classroom 2' },
            ])
  end

  it do
    expect(
      Student
        .left_outer_joins(:classrooms)
        .select(
          :id,
          :name,
          Classroom.arel_table[:id].as('as_classroom_id'),
          Classroom.arel_table[:name].as('as_classroom_name'),
        )
        .where(
          classrooms: { id: [1, 2] }
        )
        .order(
          Student.arel_table[:id].asc,
          Arel.sql('as_classroom_id').asc
        )
        .tap { |sql| p sql.to_sql }
        .as_json
        .map(&:symbolize_keys)
    ).to eq([
              { id: 1, name: 'Student 0', as_classroom_id: 1, as_classroom_name: 'Classroom 0' },
              { id: 1, name: 'Student 0', as_classroom_id: 2, as_classroom_name: 'Classroom 1' },

              { id: 2, name: 'Student 1', as_classroom_id: 1, as_classroom_name: 'Classroom 0' },
              { id: 2, name: 'Student 1', as_classroom_id: 2, as_classroom_name: 'Classroom 1' },

              { id: 3, name: 'Student 2', as_classroom_id: 2, as_classroom_name: 'Classroom 1' },
            ])
  end
end

         SELECT `student`.`id`, 
                `student`.`name`, 
                `classroom`.`id`  AS as_classroom_id, 
                `classroom`.`name`AS as_classroom_name 
           FROM `student`
LEFT OUTER JOIN `student_classroom`
             ON `student_classroom`.`student_id`= `student`.`id`
LEFT OUTER JOIN `classroom`
             ON `classroom`.`id`= `student_classroom`.`classroom_id`
       ORDER BY `student`.`id`  ASC,
                as_classroom_id ASC