Querying belongs_to Associations

We're going to start out by discussing advanced techniques for querying our belongs_to associations.

Our example's domain model

If you're taking this course, you probably have ActiveRecord models and associations in those models. We'll be using the following simple but hopefully familiar-looking domain for our examples:

class Person < ActiveRecord::Base
  belongs_to :role
end

class Role < ActiveRecord::Base
  has_many :people
end

Throughout the course, we'll be showing examples of how to use ActiveRecord methods to query your database:

Person.all

along with the SQL that is generated:

SELECT "people".*
FROM "people";

and what the resulting data would look like:

id	name	role_id
1	Wendell	1
2	Christie	1
3	Eve	2

As you can see, we have a few people in our table, each with a role_id that corresponds to the primary key of a row in the roles table. In addition, roles have a name and a boolean column called billable.

Find all people who belong to a billable role

Our first challenge: find all people who belong to a billable role.

This challenge, and most of the challenges in this course, can easily be solved with plain old Ruby Enumerable methods, but our goal is to learn how to get the database to do the heavy lifting for us via ActiveRecord. This will be much more efficient than something like:

# This works, but is not optimal
Person.all.select { |person| person.role.billable? }

We've all done this kind of thing before, but this isn't optimal for a few reasons:

• We're hitting the database potentially hundreds or thousands or millions of times (for each person, we're making a request to the roles table).
• Our application is retrieving more data from the database than it actually needs (we don't really care about attributes of roles, we just want a filtered list of people).
• We're building lots of memory-hungry ActiveRecord Role objects that we don't need.

Gluing tables together with the joins method

The first tool we'll learn about to improve this situation is ActiveRecord's joins method. This method allows us to tell ActiveRecord to perform a SQL join on its associations:

Person.all.joins(:role)

which generates SQL like this:

SELECT "people".*
FROM "people"
INNER JOIN "roles"
  ON "roles.id" = "people"."role_id";

and retrieves data like this:

people			roles
id	name	role_id	id	name	billable
1	Wendell	1	1	Developer	t
2	Christie	1	1	Developer	t
3	Eve	2	2	Manager	f

Available data vs returned data

The data on the right in gray is from the roles table, and is being "joined" onto the people table (matching up the primary key with the foreign key).

Those columns are now available while in the database to query against, but will not be sent back to the application and built into ActiveRecord objects.

This helps with transaction speed, processing time, and memory usage: much better than our plain old Ruby solution.

Filtering with the where method

Now that we have the roles-related columns virtually joined to our people table, we can filter our list the way that we want to, with ActiveRecord's where method:

Person.all.joins(:role).where(roles: { billable: true })

which generates SQL like this:

SELECT "people".*
FROM "people"
INNER JOIN "roles"
  ON "roles.id" = "people"."role_id"
WHERE "roles"."billable" = 't';

and retrieves data like this:

id	name	role_id	id	name	billable
1	Wendell	1	1	Developer	t
2	Christie	1	1	Developer	t

Success! We've achieved our goal, we're only hitting the database once, and we're only building the ActiveRecord objects that we need -- two Person objects, and nothing from roles.

Normalization of SQL by ActiveRecord

We're using Postgres for these examples, and SQL is mostly standardized across databases -- but not quite. In particular, in this example, 't' means true, but databases other than Postgres might have slightly different syntaxes.

Fortunately, ActiveRecord will handle the gory details of translating to whatever SQL syntax the database being used requires.

Separating concerns with the merge method

What we have so far works, but it feels like the logic of what defines a billable role should live in the Role model, rather than right here in a query about people.

First, let's define a method that returns the relation that we want:

class Role < ActiveRecord::Base
  def self.billable
    where(billable: true)
  end
end

And now, when we're querying people, we can use ActiveRecord's merge method to leverage this relation:

Person.joins(:role).merge(Role.billable)

This produces exactly the same SQL as our first solution, but has the advantage of separating concerns better. Note that we still need to join :role, since the merged relation requires columns from that table; otherwise, you'll see this (perhaps familiar) error message:

PG:UndefinedTable: ERROR: missing FROM-clause entry for table "roles"
LINE 1: SELECT * FROM "people" WHERE "roles"."billable" = 't'
                                     ^
: SELECT * FROM "people" WHERE "roles"."billable" = 't'

Complete Solution

Now we can package our complete solution up in a nice method:

class Person < ActiveRecord::Base
  def self.billable
    joins(:role).merge(Role.billable)
  end
end

And we're done! Now we can very cleanly and efficiently retrieve all billable people with:

Person.billable

which generates SQL like this:

SELECT "people".*
FROM "people"
INNER JOIN "roles"
  ON "roles.id" = "people"."role_id"
WHERE "roles"."billable" = 't';

and retrieves data like this:

id	name	role_id	id	name	billable
1	Wendell	1	1	Developer	t
2	Christie	1	1	Developer	t

That's it for querying belongs_to associations; we have an exercise for you to try out next, and then we'll see you again to learn about querying has_many associations.

Good luck!