S Q L through Active Record, Pt. Three

Thanks again to Giraffe Academy for it’s free S Q L course, whose modeling and seed data I have here adapted to a Rails application.
The previous part of this series.
We last left off generating models and their corresponding tables to our My S Q L database. Now it’s time to associate those models and start querying. We’ll dissect the diagram from the previous part of this series, establishing associations one by one. Then, compare querying in a Rails console versus My S Q L’s
According to our domain map, the Employee model participates in every join. It stands at the center, connecting Client and Branch, as well as one Employee instance to another. Let’s start there. In cmon-learn-some-sql/app/models/employee.rb we’ll connect an employee to it’s branch:
class Employee < ApplicationRecord
has_one :branch_employee
has_one :branch, through: :branch_employee
endWorking our way over to Branch through the join BranchEmployee, add the following to cmon-learn-some-sql/app/models/branch_employee.rb:
class BranchEmployee < ApplicationRecord
belongs_to :employee
belongs_to :branch
endTo seal the deal, we’ll tell the branch about the employees it has. In cmon-learn-some-sql/app/models/branch.rb:
class Branch < ApplicationRecord
has_many :branch_employees
has_many :employees, through: :branch_employees
endNow to connect Employee to Client. Go back to cmon-learn-some-sql/app/models/employee.rb. We’re going to add two lines. The whole model after the update is shown below, not just the two lines we’re adding.
class Employee < ApplicationRecord
has_one :branch_employee
has_one :branch, through: :branch_employee has_many :sales
has_many :clients, through: :sales
end
We’ll give Sale to both Employee and Client. In cmon-learn-some-sql/app/models/sale.rb :
class Sale < ApplicationRecord
belongs_to :employee
belongs_to :client
endNow tell about cmon-learn-some-sql/app/models/client.rb it:
class Client < ApplicationRecord
has_many :sales
has_many :employees, through: :sales
endEmployees are now associate with their clients and branch, leaving us only to differentiate associations between employees themselves. We’ll return tocmon-learn-some-sql/app/models/employee.rb one last time, adding self joins with aliased methods.
class Employee < ApplicationRecord
has_one :branch_employee
has_one :branch, through: :branch_employee has_many :sales
has_many :clients, through: :sales has_one :inferior_superior, foreign_key: :inferior_id, class_name: "SuperiorInferior"
has_one :superior, through: :inferior_superior has_many :superior_inferiors, foreign_key: :superior_id, class_name: "SuperiorInferior"
has_many :inferiors, through: :superior_inferiors
end
We created a special join for this, at cmon-learn-some-sql/app/models/superior_inferior.rb. There, let’s wrap the final association up:
class SuperiorInferior < ApplicationRecord
belongs_to :superior, class_name: "Employee"
belongs_to :inferior, class_name: "Employee"
endWow! That was quick. What did we just do? The short answer is, we created methods for our models that that write and execute S Q L for us. Below is a seed file that you can copy and paste into cmon-learn-some-sql/db/seeds.rb to test out the associations we established above.
# BRANCHESbran1 = Branch.create(name: 'Corporate')
bran2 = Branch.create(name: 'Scranton')
bran3 = Branch.create(name: 'Stamford')# EMPLOYEESemp1 = Employee.create(name: 'David Wallace', birth_date: '1661-05-11', salary: 250000, manager: true)
emp2 = Employee.create(name: 'Jan Levinson', birth_date: '1961-05-11', salary: 110000, manager: false)
emp3 = Employee.create(name: 'Angela Martin', birth_date: '1971-06-25', salary: 63000, manager: false)
emp4 = Employee.create(name: 'Kelly Kapoor', birth_date: '1980-02-05', salary: 55000, manager: false)
emp5 = Employee.create(name: 'Stanley Hudson', birth_date: '1958-02-19', salary: 69000, manager: false)
emp6 = Employee.create(name: 'Josh Porter', birth_date: '1969-09-05', salary: 78000, manager: true)
emp7 = Employee.create(name: 'Andy Bernard', birth_date: '1973-07-22', salary: 65000, manager: false)
emp8 = Employee.create(name: 'Jim Halpert', birth_date: '1978-10-01', salary: 71000, manager: false)
emp9 = Employee.create(name: 'Pam Beesly', birth_date: '1988-02-19', salary: 69000, manager: false)
emp10 = Employee.create(name: 'Oscar Martinez', birth_date: '1968-02-19', salary: 69000, manager: false)
emp11 = Employee.create(name: 'Michael Scott', birth_date: '1964-03-15', salary: 75000, manager: true)# CLIENTScli1 = Client.create(name: 'Dunmore Highschool')
cli2 = Client.create(name: 'Lackawana County')
cli3 = Client.create(name: 'FedEx')
cli4 = Client.create(name: 'John Daly, Law LLC')
cli5 = Client.create(name: 'Scranton Whitepages')
cli6 = Client.create(name: 'Times Newspaper')# BRANCH EMPLOYEESbran1.employees << emp1
bran1.employees << emp2bran2.employees << emp3
bran2.employees << emp4
bran2.employees << emp5
bran2.employees << emp9
bran2.employees << emp10
bran2.employees << emp11bran3.employees << emp6
bran3.employees << emp7
bran3.employees << emp8# COMPANY HIERARCHYemp1.inferiors << emp2emp2.inferiors << emp6
emp2.inferiors << emp11emp6.inferiors << emp7
emp6.inferiors << emp8emp11.inferiors << emp3
emp11.inferiors << emp4
emp11.inferiors << emp5
emp11.inferiors << emp9
emp11.inferiors << emp10# SALESSale.create(employee_id: emp5.id, client_id: cli1.id, amount: 5000)
Sale.create(employee_id: emp5.id, client_id: cli3.id, amount: 12000)
Sale.create(employee_id: emp5.id, client_id: cli5.id, amount: 17000)Sale.create(employee_id: emp7.id, client_id: cli4.id, amount: 11000)
Sale.create(employee_id: emp7.id, client_id: cli5.id, amount: 7500)Sale.create(employee_id: emp8.id, client_id: cli3.id, amount: 12000)
Sale.create(employee_id: emp8.id, client_id: cli4.id, amount: 15000)Sale.create(employee_id: emp11.id, client_id: cli2.id, amount: 175000)
Sale.create(employee_id: emp11.id, client_id: cli3.id, amount: 44000)
Sale.create(employee_id: emp11.id, client_id: cli4.id, amount: 22000)
Sale.create(employee_id: emp11.id, client_id: cli5.id, amount: 57000)
Sale.create(employee_id: emp11.id, client_id: cli6.id, amount: 86000)
In the next part, we’ll translate those methods into S Q L.
