Thoughtbot upcase: Querying has_many Associations

has_many.rb

# frozen_string_literal: true

begin
  require "bundler/inline"
rescue LoadError => e
  $stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
  raise e
end

gemfile(true) do
  source "https://rubygems.org"
  git_source(:github) { |repo| "https://github.com/#{repo}.git" }
  gem "rails", "5.2.0"
  gem "sqlite3"
end

require "active_record"
require "logger"
require 'rails'


ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :people, force: true do |table|
    table.string :name, null: false
    table.integer :role_id, null: false
    table.integer :location_id
    table.integer :manager_id
    table.integer :salary, null: false
  end

  create_table :roles, force: true do |table|
    table.string :name, null: false
    table.boolean :billable, null: false
  end

  create_table :locations, force: true do |table|
    table.string :name, null: false
    table.integer :region_id, null: false
  end

  create_table :regions, force: true do |table|
    table.string :name, null: false
  end
end

class Person < ActiveRecord::Base
  belongs_to :location
  belongs_to :role
  belongs_to :manager, class_name: "Person", foreign_key: :manager_id
  has_many :employees, class_name: "Person", foreign_key: :manager_id

  def self.order_by_location_name
    joins(:location).merge(Location.order(:name))
  end

  def self.with_employees
    joins(:employees).distinct
  end

  def self.with_employees_order_by_location_name
    from(Person.with_employees, :people).order_by_location_name
  end
end

class Location < ActiveRecord::Base
  belongs_to :region
  has_many :people
end

class Region < ActiveRecord::Base
  has_many :locations
end

class Role < ActiveRecord::Base
  has_many :people
end

region1 = Region.create(name: 'region1')
region2 = Region.create(name: 'region2')

role1 = Role.create(name: 'role1', billable: true)
role2 = Role.create(name: 'role2', billable: false)

locations = [
  Location.create(name: "location1", region_id: region1.id),
  Location.create(name: "location3", region_id: region1.id),
  Location.create(name: "location2", region_id: region2.id)
]
managers = locations.map do |location|
  Person.create(name: "manager-at-#{location.name}", location_id: location.id, role_id: role1.id, salary: false)
end
managers.each do |manager|
  2.times do
    Person.create(name: "employee-of-#{manager.name}", manager_id: manager.id, role_id: role2.id, salary: false)
  end
end

puts ''
puts "----------------Performing Person.all"
puts Person.all

puts ''
puts "----------------What happens with the old good ruby query "
puts "----------------Performing Person.all.select { |person| person.role.billable? }"
puts Person.all.select { |person| person.role.billable? }

puts ''
puts '----------------A better way would be to glue tables together so ActiveRecord performs a SQL join on its associations'
puts "----------------Performing Person.order_by_location_name"
puts Person.order_by_location_name

puts ''
puts "----------------And when we select the managers"
puts "----------------Performing Person.with_employees"
puts Person.with_employees

puts ''
puts "----------------What happens if we chain the filters"
puts "----------------Performing Person.with_employees.order_by_location_name"
puts Person.with_employees.order_by_location_name

puts ''
puts "----------------Now let's see what happens when we create a subquery that return distinct managers"
puts "----------------Performing from(Person.with_employees, :people).order_by_location_name"
puts Person.from(Person.with_employees, :people).order_by_location_name