S Q L through Active Record, Pt. Three

Joseph Syverson
Nov 4, 2019 · 4 min read
Dunder Mifflin, Paper Co, Inc

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

Working 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

To 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

Now 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

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

Now tell about cmon-learn-some-sql/app/models/client.rb it:

class Client < ApplicationRecord
has_many :sales
has_many :employees, through: :sales

Employees 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

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"

Wow! 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 << emp2
bran2.employees << emp3
bran2.employees << emp4
bran2.employees << emp5
bran2.employees << emp9
bran2.employees << emp10
bran2.employees << emp11
bran3.employees << emp6
bran3.employees << emp7
bran3.employees << emp8
# COMPANY HIERARCHYemp1.inferiors << emp2emp2.inferiors << emp6
emp2.inferiors << emp11
emp6.inferiors << emp7
emp6.inferiors << emp8
emp11.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.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade