ActiveRecord, Part 2

When talking to my classmates about writing difficult ActiveRecord queries, I commonly heard that when they started one it looked something like this:

class Weather
    def weather_on_most_popular_day
        binding.pry
    end 
end

Great. So first instinct when attempting a complex ActiveRecord query was to jump into a pry session and start making guesses. This strategy didn't make me feel great. While I would usually end up with what I wanted, my numerous, time-consuming guesses were based on hunches. I didn’t have a process. 

The phrase "draw the owl" is when, say, a tutorial shows you Step 1 and Step 10, but none of the implicit steps between. This "process" is often left up to you to determine.

My (new and improved) ActiveRecord Process

The amazing thing about computer programming is that people can have completely different processes and arrive at the same conclusion. I have fine-tuned my own process for attacking ActiveRecord queries and put it into concrete steps below. That being said, you may develop a very different process. Feel free to share yours in the comments section!

QUESTION 1: What do I want back?
Do I want an array or a hash? An object or a collection of objects? What attributes will these objects have? Will there be any “virtual attributes” (newly created attributes) that I want on these objects? Or do I merely want a calculation returned?

QUESTION 2: Which model will the method live on?
This can get tricky because there is no one right answer. You can access all models from any model, but the question becomes which model is most efficient to start from? In general, I put the method on the model of what I want returned from Q1.

QUESTION 3: Will this be a class or an instance method?
I did not realize this until I started to learn other languages, but in Ruby a class vs. an instance method is an important distinction. When working on a collection of objects, use a class method and begin the method name with "self." An instance method is used when working on one instance of an object. 

QUESTION 4: What columns do I need from each table?
What columns do I need to touch from each table in order to get what I want returned? This is where I like to draw. I'll sketch my schema, then circle the columns that I need and write what AR methods I think I will use.

Let's go back to the Harry Potter repository we used form Part 1 to start practicing the process. Below is my process for 4 different queries. Hopefully you won't feel like I'm just telling you to draw Hedwig.

---

1. Return all the subjects that have at least one Gryffindor in them:

Q1: What do I want back? A collection of Subject objects with its id, name, room number and teacher id.

Q2: Which model will the method live on? Since I want a collection of subject objects, I will put the method on the Subject model.

Q3: Will this be a class or an instance method? I am working on a collection of subject objects. This will be a class method.

Q4: What tables and columns do I need?

Using the above sketch, let's start with joining Subjects to Students, and then filtering the house with .where. *NOTE: I don't need to join Subjects to Enrollments and then join Enrollments to Students. I can join Subjects directly to Students because I set up Enrollments as a joins table with the appropriate ActiveRecord associations (check the repo to see exactly how I did this).

def self.subjects_with(house_name)
    joins(:students)
    .where(students: {house: house_name})
end

irb> Subject.subjects_with('Gryffindor')
=> #<ActiveRecord::Relation [#<Subject id: 9, name: "Defense Against the Dark Arts", room_number: 1, teacher_id: 3>, #<Subject id: 2, name: "Transfiguration", room_number: 1, teacher_id: 1>, #<Subject id: 3, name: "Charms", room_number: 2, teacher_id: 2>, #<Subject id: 5, name: "Potions", room_number: 0, teacher_id: 3>, #<Subject id: 1, name: "Herbology", room_number: 12, teacher_id: 5>, #<Subject id: 2, name: "Transfiguration", room_number: 1, teacher_id: 1>, #<Subject id: 5, name: "Potions", room_number: 0, teacher_id: 3>, #<Subject id: 6, name: "Muggle Studies", room_number: 8, teacher_id: 7>, #<Subject id: 1, name: "Herbology", room_number: 12, teacher_id: 5>, #<Subject id: 2, name: "Transfiguration", room_number: 1, teacher_id: 1>]>

This seems to be getting us what we want. But if you look closely at the result, you'll notice duplicated subjects. We can easily remedy this by adding the handy AR method .distinct:

def self.subjects_with(house_name)
    .joins(:students)
    .distinct
    .where(students: {house: house_name})
end

irb> Subject.subjects_with('Gryffindor')
=> #<ActiveRecord::Relation [#<Subject id: 1, name: "Herbology", room_number: 12, teacher_id: 5>, #<Subject id: 2, name: "Transfiguration", room_number: 1, teacher_id: 1>, #<Subject id: 3, name: "Charms", room_number: 2, teacher_id: 2>, #<Subject id: 5, name: "Potions", room_number: 0, teacher_id: 3>, #<Subject id: 6, name: "Muggle Studies", room_number: 8, teacher_id: 7>, #<Subject id: 9, name: "Defense Against the Dark Arts", room_number: 1, teacher_id: 3>]>

---

2. Return the breakdown of students in a given subject from each house:

Q1: What do I want back? A hash with the keys as the house and the values as the count of students from that house.

Q2: Which model will the method live on? This is a specific instance of a subject, so the method should live on the Subject model.

Q3: Will this be a class or an instance method? Instance method.

Q4 What tables and columns do I need? I won't sketch this one out because we only need the house column on the Students table.

def breakdown_students
    students.group(“house”).count
end

irb> Subject.first.breakdown_students
=> {"Gryffindor"=>2, "Slytherin"=>2}

Like example #1, this method takes advantage of ActiveRecord's associations. I can call .students within this method, just like I can call transfiguration.students on an instance of a subject. A Subject has many Students through Enrollments, therefore I must have these has_many relationships on the Subject and Student models for this query to work. See the repo for clarification.

---

3. List student name and grade IF grade is lower than the average:

Q1: What do I want back? An array of student objects with their name and average grade as attributes.

Q2: Which model will the method live on?  This could actually live on either Student OR Enrollment. I'm going to put it on Student because I want to get student objects back.

Q3: Will this be a class or an instance method? I'm working on the whole student body, so this will be a class method.

Q4: What tables and columns do I need?

def self.below_average
    select("name, avg(grade) AS average_grade")
    .joins(:enrollments)
    .group(:name)
    .order("avg(grade) desc")
    .having("avg(grade) < ?",  Enrollment.average_grade)
end

irb> Student.below_average
=> #<ActiveRecord::Relation [#<Student id: nil, name: "Vincent Crabbe">, #<Student id: nil, name: "Hannah Abbott">, #<Student id: nil, name: "Justin Finch-Fletchley">, #<Student id: nil, name: "Ernie Macmillan">, #<Student id: nil, name: "Ron Weasley">, #<Student id: nil, name: "Gilderoy Lockheart">, #<Student id: nil, name: "Gregory Goyle">]>

A few things to note about this method:

First, you can think of .where and .having as similar because they both filter out records based on some condition. The difference is that .having filters records after aggregation, and .where filters records before the aggregation. 

Second, this query contains a subquery within .having. The subquery is combination of SQL and AR syntax: this is necessary since AR doesn't give you all the functionalities that SQL does. You can think of the ? as a parameter that gets passed the proceeding argument.

Third, remember that we can still access the average_grade virtual attribute even though it does not appear on each ActiveRecord Relation. Call "Student.below_average.first.average_grade" and you'll see that Vincent Crabbe has an average grade of 82%. That's really not bad considering his reputation.

---

4. Name the teacher with lowest class average... but only if their class has Gryffindors

Weird question. But maybe we have a sneaking suspicion that Gryffindors are being treated unfairly by a certain teacher. Let's find out!

Discover & share this Movies GIF with everyone you know. GIPHY is how you search, share, discover, and create GIFs.

Q1: What do I want back? A teacher object that includes his/her name.

Q2: Which model will the method live on?  As you can see from my sketch, we have a lot of models to choose from. But if we stick to the standard of placing the method on the same model we want returned, we're golden. Teacher it is.

Q3: Will this be a class or an instance method? I'm working on the collection of teachers, so this will be a class method.

Q4: What tables and columns do I need?

There's a lot going on here. We actually have to use ALL our tables in our schema to get what we need. There's a few things at play you may not have seen before:

First, we can group by 2 attributes.

Second, we are performing joins on joins on joins. Sometimes .joins can be tricky because the tables being joined need to be singular or plural depending on their relationship. 

def self.lowest_class_average_with(house)
    select("teachers.*, avg(enrollments.grade) AS average_grade")
    .joins(subjects: [enrollments: :student])
    .where(students: {house: house})
    .group(:id, :subject_id)
    .order("average_grade")
    .first
end

irb> Teacher.lowest_class_average_with('Gryffindor')
=> #<Teacher id: 2, name: "Flitwick">

And there you have it: our theory is WRONG. C'mon guys, Snape really wasn't that bad.

Discover & share this Snape GIF with everyone you know. GIPHY is how you search, share, discover, and create GIFs.