165 ActiveRecord scopes [4d]

In this card we will learn to write code that scales with a large number of database records. We will do this by pushing work out of Ruby and into the database.

Important

Work on this lesson in teacher mode.

Learning goals

• You can explain what a scope is and how it differs from an array of records, including when a scope actually runs its query (e.g. on each, but not on where).
• You can build a query by composing scopes — filtering, ordering, limiting, joining — using ActiveRecord's relation methods, and read the SQL it will produce with to_sql.
• You can define named scopes and write query methods that return scopes, so callers can keep composing.
• You can explain the N+1 query problem, recognize it in the log, and fix it with eager loading (e.g. preload), and catch remaining lazy loads with strict_loading.
• You let the database do the work — filtering, counting, aggregating — instead of loading records into Ruby, using relation methods like where, count or pluck.
• You can build a list or report whose number of queries stays constant regardless of the amount of data, e.g. with a few simple queries whose results you combine in Ruby.
• You check how many queries your code makes before you consider it done.
• You can explain why we avoid default_scope and prefer named scopes.

Note

We don't like to use default scopes Show archive.org snapshot in our projects. It can be confusing when new scopes inherit conditions out of nowhere.

If an app does have a frequently used condition (like ignoring all archived posts), we define a named scope like Post.active and use that.

Resources

Read what's new to you, skim what's familiar, skip what you already master. Stop when you can meet the learning goals.

Your agent can also generate an overview, a tutorial or an explanation for anything here, tailored to what you already know. Just ask.

• 📄 Rails Guide: Active Record Query Interface Show archive.org snapshot — the reference for everything on this card; read Scopes Show archive.org snapshot and Eager Loading Associations Show archive.org snapshot closely
• 📄 Battling N+1 queries in Rails Show archive.org snapshot — recognizing N+1 in the log and the tools that catch it (2024)
• 📄 Rails loading patterns: eager, lazy and strict loading Show archive.org snapshot — preload vs. includes vs. eager_load, and strict_loading as a safety net
• 📄 ActiveRecord: Specifying conditions on an associated table — our card
• 📄 Rails API: default_scope Show archive.org snapshot — read the caveats; this is why we avoid it
• 📄 Rails API: Enumerable#index_by and #group_by Show archive.org snapshot — for composing several simple queries in Ruby
• 📘 The chapter "Relearning ActiveRecord" from Growing Rails Applications in Practice Show archive.org snapshot (in our library)

Tips

When scopes load

A scope like User.where(email: 'foo@bar.com') does not make an SQL query. It simply returns a scope object for further chaining with #where, #order, etc.

It can be confusing that typing a scope into the Rails console will immediately load results from the database. This is due to the console automatically inspecting Show archive.org snapshot the last statement, in an attempt to visualize the results.

To prevent this, add any other expression after the scope:

scope = Movie.all; nil

A scope runs its query as soon as you need the records, e.g. when you call each, map or first on it. We still call to_a explicitly before we work with the records. It marks the point where the code stops building a query and starts working with an array. From there on, use Ruby's array methods (e.g. select, not where):

movies = Movie.where(year: 1995..).order(:title).to_a # the query runs here
movies.select { |movie| movie.title.length > 10 }     # Ruby array methods from here on

Call #to_sql on a scope to see the query it will produce. This does not actually run a query:

Card.where(title: 'Foo').to_sql
# => SELECT * FROM cards WHERE title="Foo"

Advanced conditions

You can pass an array as a value in #where. ActiveRecord will make an IN() Show archive.org snapshot query:

Card.where(id: [1,2,3]).to_sql
# => SELECT * FROM cards WHERE id IN (1, 2, 3)

Instead of an array, you can pass a scope that selects a single column. ActiveRecord then makes a subquery, and the database does all the work in one query:

Movie.where(id: Role.where(actor_id: 7).select(:movie_id)).to_sql
# => SELECT * FROM movies WHERE id IN (SELECT movie_id FROM roles WHERE actor_id = 7)

Don't use pluck for this: Movie.where(id: Role.where(actor_id: 7).pluck(:movie_id)) runs two queries and sends every id through Ruby.

If you need an operator that is not equality (=) or inclusion (IN()) you cannot pass a hash to where. You need to pass an SQL fragment instead:

Card.where('id > ?', 5).to_sql
# => SELECT * FROM cards WHERE id > 5

Chaining multiple #where will AND the conditions:

Card.where(id: [1,2,3]).where(title: 'Foo').to_sql
# => SELECT * FROM cards WHERE id IN (1, 2, 3) AND title="Foo"

Preloading associations

When you preload associations with #preload or #includes Rails will make one query for each table:

Card.where(title: 'Foo').preload(:user).to_a  

# SELECT * FROM cards WHERE title="Foo"
#
# | id | user_id | title
# +----+---------+-------------------
# |  2 |    4003 | Foo
# |  4 |    4005 | Foo
#    
# SELECT * FROM users WHERE id IN (4003, 4005)

Catching lazy loads with strict_loading

#strict_loading makes every lazy association load raise an error, so an N+1 query fails loudly instead of slowly. Assuming movies have many roles, each with an actor:

movie = Movie.strict_loading.find(1)
movie.roles.each { |role| role.actor.name }
# raises ActiveRecord::StrictLoadingViolationError

movie = Movie.strict_loading.preload(roles: :actor).find(1)
movie.roles.each { |role| role.actor.name }
# works, with one query per table

You can also turn it on for a single association (has_many :roles, strict_loading: true) or for the whole app (config.active_record.strict_loading_by_default = true). With config.active_record.action_on_strict_loading_violation = :log, violations go to the log instead of raising an error.

Plucking columns

You can use #pluck to fetch a single column from a scope's result set. The result is returned as an array:

user_ids = Card.where(title: 'Foo').pluck(:user_id)
# => [4003, 4005]

This is much faster than loading an array of Card records!

A common technique is to use an array of plucked foreign keys in another query:

User.where(id: user_ids).to_sql
# => SELECT * FROM users WHERE id IN (4003, 4005)

This way, instead of one complicated query with many JOINs, you use multiple but simple queries. This can be easier to understand and may even perform better.

Exercises

Cards query (makandra only)

Ask your mentor for a dump of the staging target — trainees have no server access — and load it into your local database Show archive.org snapshot .

In the makandra cards app, load an array of all Users that are an admin in a deck whose slug begins with makandra (e.g. makandra-besprechungen).

Try to write as little SQL as possible. Wherever possible, define conditions using #where(hash). If you do need to use an SQL fragment, make it as short as possible.

It's OK to do to use more than one query (or one scope) for this exercises. However, your solution should always use the number of queries, regardless of the number of decks.

Search for MovieDB

In Testing basics you implemented Movie.search(query) for your MovieDB. If you haven't already, make sure that it returns not an array but a scope that can be chained further, so all filtering happens in the database. E.g. I should be able to use it like this:

Movie.search('event -main').search('horizon').where('year > 1995').order(:year).to_a

Keep your specs from Testing basics passing, and add an example that chains search with other scopes.

Catch N+1 queries in MovieDB

Wherever MovieDB lists associated records, e.g. the cast on a movie's page, load them with strict_loading and fix every violation with preload.

Open budgets in Project Hero (makandra only)

In the Project Hero app, build a view that lists all projects that have open (unclosed) budgets with unused amounts:

+---------------+-----------------------------------+
| Project       | Unused amount in all open budgets |
+---------------+-----------------------------------+
| Foo           | 200 €                             |
| Bar           | 150.000 €                         |
| Baz           | 90.000 €                          |
+---------------+-----------------------------------+

Rows for projects with more than 2500 € of unused budget should be highlighted visually.

To get realistic data for this exercise, ask your mentor for a dump of the makandra stage and load it into your local database Show archive.org snapshot .

The models you need are Project, Budget and Activity; look at their associations in schema.rb before you start.

Push your changes into a branch prefixed with your initials and a slash (/). E.g. if your name is John Doe push it into a branch jd/curriculum-open-budgets.

Version 1: OOP

First, build it naively by iterating over all projects, iterating over the budgets and activities of each project, and summing up amounts in Ruby.

When you're done, run tail -f log/development.log, reload the report and observe the performance of your view:

• How long does your code need to compute the report?
• How many SQL queries does it trigger?
• How many Project, Budget and Activity objects are instantiated to handle the request?

Version 2: With scopes

Now build a version of your view that does the same number of queries regardless of how many projects, budgets, activities, etc. you have.

This new version should also not instantiate thousands of ActiveRecord objects, regardless of the database size.

Write down the numbers for both versions: queries, time, instantiated objects. That before/after pair is the point of this card.

Tip

You don't need to produce the report in a single query! Consider running multiple separate queries, then compose the results in Ruby. The #index_by Show archive.org snapshot and #group_by Show archive.org snapshot methods are useful for that.

When the report works: Can you move your code to a OpenBudgetReport class with a nice API? For this the OpenBudgetReport needs to do as much work as possible and offer comfortable and minimum methods to its callers (the controller and view).

Note

For the purpose of this exercise, please don't reuse any existing classes that solve a similar problem in Project Hero, such as BudgetCalculator or Analysis::Budgets.