ActiveRecord Cannot Read Minds

TL;DR

• ActiveRecord has tons of nice, declarative abstractions on top of SQL
• It can be dangerous to assume what those abstractions produce

Automatic Association Recognition in Where Clauses

ActiveRecord is great at noticing what is meant when trying to filter a query by a record. If
we have the following two models:

class User < ApplicationRecord
  has_many :pets
end

class Pet < ApplicationRecord
  belongs_to :user
end

We can do:

user = User.first
pets = Pet.where(user_id: user.id)

which generates the SQL:

pets.to_sql
# => SELECT "pets".* FROM "pets" WHERE "pets"."user_id" = 1

because user_id is a column of the pets table. But, for cleanliness, we can simplify this to:

user = User.first
pets = Pet.where(user: user)

This correctly generates the same SQL! And, it even works for has_many associations!

pets = Pet.all
pets = User.where(pets: pets)

Which generates SQL like:

pets.to_sql
# => SELECT "users".* FROM "users" WHERE "users"."id" IN (SELECT "pets"."user_id" FROM "pets")

That's great! ActiveRecord took the words describing what we wanted and converted it to SQL for us! But does this always work the way we expect? Let's consider this setup of users
that have animals with names:

User.create(first_name: "Hank").tap do |hank|
  hank.pets.create(breed: "cat", name: "Nala")
  hank.pets.create(breed: "dog", name: "Max")
end

User.create(first_name: "Bob").tap do |bob|
  bob.pets.create(breed: "dog", name: "Nala")
  bob.pets.create(breed: "cat", name: "Felix")
end

Let's say we want to know which users own pets named "Nala" and what breed those "Nala"s are:

nalas = Pet.where(name: "Nala")
User
  .joins(:pets)
  .where(pets: nalas)
  .pluck("users.first_name", "pets.breed")

And one might expect the output to look like:

[["Hank", "cat"], ["Bob", "dog"]]

But instead what we get is:

[["Hank", "dog"], ["Hank", "cat"], ["Bob", "cat"], ["Bob", "dog"]]

What happened? Well, one issue with trying to convert words into SQL is that there are different ways to interpret words. What ActiveRecord got was:

1. Get users that have at least one pet whose name is "Nala" (Hank and Bob)
2. Join them to pets to get all pets for both users
3. Print the name of each person and the breed of each pet that person has

Which is a perfectly valid interpretation and it's very easy to write tests without sufficient
setup data to catch this (trust me; I've done it!). Instead we could write ActiveRecord like:

nalas = Pet.where(name: "Nala")
User
  .joins(:pets)
  .where(pets: { id: nalas })  .pluck("users.first_name", "pets.breed")

or:

nalas = Pet.where(name: "Nala")
User
  .joins(:pets)
  .merge(nalas)  .pluck("users.first_name", "pets.breed")

Summary

It's important to review the SQL generated by ActiveRecord clauses carefully along with
the words describing the desired data.

Learn more about how The Gnar builds Ruby on Rails applications.