Lesson Tuesday

In this lesson, we'll cover another powerful and convenient feature of Active Record: the ability to write database scopes. A scope is a class method that makes a database query for us. Let's make a scope for our record store application. Let's say that we want a scope that filters albums that have "Rock" as their genre property. We'd put the scope in the Album class and it would look like this:

models/album.rb
class Album < ApplicationRecord
  scope :rock, -> { where(genre: "Rock") }
...
end

Instead of passing a block to the scope method, we use the following operator ->. The -> operator is colloquially known as the stabby lambda. It turns a block into an object called a lambda that can be passed around just like any other object. Lambdas are a more advanced concept that we won't cover in detail in this course. For now, it's just important to know that they are part of the syntax for an Active Record scope.

The scope method runs when the class is loaded that dynamically creates a method on the class. In this case, scope(:rock) creates an Album.rock method that returns all rock albums.

We could also write this as a class method as well:

models/album.rb
...
  def self.rock
    where(genre: "Rock")
  end
...

We can test queries in the Rails console before we make them into scopes. Here's this same query in the console:

Album.where(genre: "Rock")

Practicing Scopes


In this section, we'll explore how to write a few complex scopes. The following examples imagine a project tracking tool for managers at a large enterprise. Each worker can have many tasks and a task can belong to one worker (a one-to-many relationship). You are not required to actually build this project. Instead, read through the examples and try incorporating these scopes in your own applications over the next few days.

Some of these examples are complex and may be overwhelming at first. Remember that documentation is your friend and that it's often necessary to do some research to figure out more complex queries.

Application Schema

The schema for this theoretical application looks like this:

schema.rb
 create_table "workers", force: :cascade do |t|
    t.string   "name"
    t.string   "role"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "tasks", force: :cascade do |t|
    t.string   "description"
    t.integer  "worker_id"
    t.boolean  "done"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

The managers would like to have some tracking functionality in the application. We'll start by writing sample queries in the Rails console and then translate them into scopes. Once again, it will be up to you to translate these scopes and make these work in your own applications. This act of translation can help us better understand how these scopes work and can also be helpful practice for using real world documentation, which almost always involves code which we have to translate and adapt to our own applications.

Which Tasks were Added Today?

Here’s the query in the console:

Task.where("created_at >=?", Time.now.beginning_of_day)

In this query, Time.now.beginning_of_day will be passed in as the ?. In other words, the query will only return tasks with a timestamp greater than the time at the beginning of today.

Here's our scope:

task.rb
class Task < ApplicationRecord
  scope :today, -> { where("created_at >=?", Time.now.beginning_of_day)}
end

Now we can call Task.today in our application to return today's tasks.

What are the 10 Most Recently Added Tasks?

Here it is in the console:

Task.order(created_at: :desc).limit(10)

We order the tasks descending by the created_at field, returning a limit of 10 records.

And here's our scope:

scope :ten_most_recent, -> { order(created_at: :desc).limit(10)}

Which Workers Have the Most Tasks?

This is a tough one but it's potentially very useful information for the managers. Perhaps some workers are getting too many tasks and their work should be redistributed. Here's the query in the console:

Worker.select("workers.name, workers.role, count(tasks.id) as tasks_count").joins(:tasks).group("workers.id").order("tasks_count DESC").limit(10)

Here's how this query looks in the console as raw SQL:

Worker Load (5.0ms)  SELECT  workers.name, workers.role, count(tasks.id) as tasks_count FROM "workers" INNER JOIN "tasks" ON "tasks"."worker_id" = "workers"."id" GROUP BY workers.id  ORDER BY tasks_count DESC LIMIT 10

In this query, we use the SQL GROUP BY command to group workers by their total tasks. GROUP BY is generally used with aggregating functions like COUNT. (Other aggregating functions include MAX, MIN and SUM.)

Let's summarize what this query does:

  • SELECT will return a result-set with with the selected fields workers.name and workers.role. (We could remove workers.role and we'd just get the name of each worker returned from the query instead.)

  • COUNT counts tasks.id as tasks.count by making an INNER JOIN between workers and tasks. We need to do an INNER JOIN because our workers table doesn't store information about tasks; we have to count each task's worker_id to determine which worker has the most tasks.

  • GROUP BY groups our workers by id. (We could also group by name and the end result would be the same because the ORDER BY clause will then reorder our workers.)

  • ORDER BY orders our workers in DESC order by tasks_count. Our final query has a LIMIT of 10.

Here's how we'd then turn this into a scope:

worker.rb
class Worker < ApplicationRecord
  has_many :tasks

  scope :most_tasks, -> {(
    select("workers.id, workers.name, workers.role, count(tasks.id) as tasks_count")
    .joins(:tasks)
    .group("workers.id")
    .order("tasks_count DESC")
    .limit(10)
    )}
end

Notice how the query is separated into multiple lines to improve readability.

These kinds of scopes and SQL queries are common and it should be clear how this scope could be useful to a wide range of applications. If we're building a movie review site, we could use this scope to determine which movies have the most reviews so they can be featured on the home page. If we're building a social media site, this query could be used to determine which users have the most followers.

Adding a Basic Search Function

Let’s do one final scope. The managers would like to add a basic search function so they can search workers by name. We can do this with a scope by passing in a parameter:

worker.rb
scope :name, -> (name_parameter) { where(name: name_parameter) }

This works great if a manager types in an exact match, and this scope would still be very useful for fields that do have exact matches. For instance, if a task could have multiple statuses, such as inactive, active and complete, then a scope like the one above would be perfect for filtering.

Let’s say we want our search function to be more general. A manager should be able to type in "Smith" and the query should return all Smiths, including both John and Jane Smith. We can use SQL’s LIKE operator to do this:

worker.rb
scope :search, -> (name_parameter) { where("name like ?", "%#{name_parameter}%")}

Here we pass in the name_parameter to our where query with string interpolation. The % character is a wild card. It represents any number of characters. With the % at the beginning and end of our query parameter, we’re saying that "Smith" can occur anywhere in the name, whether that’s Jane Smith or John Smith Adams.

One thing to note about the scope above: it’s still case-sensitive. Try customizing these scopes to make them case-insensitive.

Take some time to challenge yourself and explore more complex scopes and queries. You should also continue to develop your SQL skills on your own, as using SQL is an important skill for backend developers, IT experts, and many other tech professions.

Check out the Rails Guide on querying conditions as well as the API documentation on query methods. Active Record's querying interface is one of its most powerful features and understanding how to make advanced queries is an important part of being a Rails developer.

Lesson 25 of 34
Last updated July 14, 2022