Getting Really Good at Rails :joins

If you’re intimidated by ActiveRecord :joins then fear not, this article will break down a step-by-step guide for understanding and using them with ease in your own code.

Why joins are important

The main motivation behind joins is to efficiently load data. With joins you can significantly reduce the time it takes to select records that fit the criteria you are looking for.

Slow code without joins

Let’s say we have 2 tables: Employee and Company where a company has many employees.

# == Schema Information
# Table name: employees
# id
# company_id
# name
# status
class Employee < ApplicationRecord
  belongs_to: :company
end
# == Schema Information
# Table name: companies
# id
# name
# verified
class Company < ApplicationRecord
  has_many: :employees
end

If we wanted to load all active employees, we would write a query like this:

Employee.where(status: "active")

Pretty simple. But what if we want to load all active employees who belong to companies that are verified? Without joins, you would write something like this:

active_employees = Employee.where(status: "active")
active_employees.select do |employee|
  employee.company.verified?
end

This isn’t great because it triggers an N + 1 query since it is looking in the database for a company record for each employee in the loop. I ran a similar query in a MySQL database containing 62 employee records and 3 company records. This unoptimized query took about 1 second to run on average.

Improvements with :joins

We can easily improve the code above by using :joins.

Employee.where(status: "active").joins(:company).where(companies: { verified: true })

Running this optimized query in the same database described from above took about 0.5 seconds on average.

Let’s break this down even more

If we were to simplify this query to:

Employee.where(status: "active").joins(:company)

We would be loading all active employees who belong to a company. If an employee is not associated with a company, then they would not be returned. This is because :joins performs a SQL inner join by default.

Adding the clause where(companies: { verified: true }) scopes companies so that only employee’s with verified companies are returned. Note that this section of the query uses companies as a keyword since it is referring to the table name. The SQL output would look like this:

SELECT `employees`.* FROM `employees` INNER JOIN `companies` ON `companies`.`id` = `employees`.`company_id` WHERE `employees`.`status` = 'active' AND `companies`.`verified` = 1

Other ways to write this clause and perform the same query would be to use a string:

Employee.where(status: "active").joins("INNER JOIN companies ON companies.id = employees.company_id WHERE companies.is_verified = 1")
Employee.joins("INNER JOIN companies ON companies.id = employees.company_id WHERE employees.status = 'active' AND companies.is_verified = 1")

Being able to pass in a custom SQL command allows for other common join operations to be performed such as left and outer joins.

Joins across tables

Let’s say we add an Address model where a company has many addresses. If we wanted to grab all employees who are at companies that have addresses, we would perform the following query:

Employee.joins(company: [:addresses])

If we want to grab all active employees at companies that are verified in the state of California, we would perform this query:

Employee.where(status: "active").joins(company: [:addresses]).where(addresses: { state: 'CA'}, companies: { verified: true })

Nested joins and where clauses can be tacked on til the cows come home.

When to use :includes

:includes is a method in ActiveRecord used to preload data in order to avoid N + 1 queries. Use :joins when you solely need to filter data based on associated tables and use :includes if you need to reference data in associated tables later on. For example, we’d want to use :includes if we were to load all active employees at companies that are verified and then print each employee’s name and their company name.

employees = Employee.where(status: "active").includes(:company).where(companies: { verified: true })
employees.each do |employee|
  puts employee.name
  puts employee.company.name
end

For more information on :includes, you can refer to A Visual Guide to Using :includes in Rails.

Joins can be a hard concept to grasp at first due to the fact that they involve at least two tables that require querying. But with practice, you will begin to develop an intuition for how they work and how to use them. If you are uncomfortable with the different types of joins (inner, outer, left, etc.), I recommend checking out A Visual Guide to SQL Joins which shows very clear-cut examples of how they each work.