Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 37 ยท Associations and Queries

The Active Record Query Interface and Scopes

Master Active Record queries: where, order, limit, select, pluck, group, joins, find_by, exists?, safe SQL placeholders and named scopes, with a runnable lazy-relation stand-in that shows how chaining works.

You almost never write SQL by hand in a Rails app. Instead you call methods on a model class, chain them, and Active Record builds the SQL. Post.where(published: true).order(created_at: :desc).limit(10) reads like a sentence and produces one SELECT. The thing that makes chaining work is the relation: an object that stores the conditions you have added so far and only talks to the database when you actually need the rows.

This lesson shows the everyday query methods, how to keep them safe from SQL injection, and how to name reusable queries as scopes. Rails cannot run here, so real code is read-only and the runnable stand-in builds a tiny lazy relation over an array. For the underlying SQL see SELECT, WHERE and GROUP BY.

Finding single records

Ruby
Post.find(3)                    # by primary key; raises ActiveRecord::RecordNotFound
Post.find_by(slug: "hello")     # first match or nil
Post.find_by!(slug: "hello")    # raises if missing
Post.first                      # ORDER BY id LIMIT 1
Post.last
Post.take                       # any row, no ordering
Post.exists?(slug: "hello")     # true/false, cheapest check

find is for ids and raises, which Rails turns into a 404 page in controllers. find_by returns nil when nothing matches, so you handle it yourself.

Building conditions with where

Ruby
Post.where(published: true)
Post.where(author_id: [1, 2, 3])                  # IN (1, 2, 3)
Post.where(created_at: 1.week.ago..)              # >= a date (endless range)
Post.where(views: 100..500)                       # BETWEEN
Post.where.not(status: "draft")
Post.where(published: true).or(Post.where(featured: true))
Post.where("views > ?", 100)                      # SQL fragment with a placeholder
Post.where("title LIKE :q", q: "%ruby%")          # named placeholder

Never interpolate user input into SQL strings. Post.where("title = '#{params[:q]}'") is an SQL injection hole. The hash form and the ? / :name placeholders escape values for you.

For LIKE searches, escape wildcards typed by the user with sanitize_sql_like:

Ruby
term = Post.sanitize_sql_like(params[:q])
Post.where("title LIKE ?", "%#{term}%")

Ordering, limiting and paging

Ruby
Post.order(created_at: :desc, id: :desc)
Post.order(:title)                 # ascending
Post.reorder(:id)                  # replace earlier order
Post.limit(20).offset(40)          # page 3 of 20
Post.reverse_order

Offsets get slow on huge tables. For infinite lists, filter with a cursor instead: Post.where("id < ?", last_seen_id).order(id: :desc).limit(20). Gems such as Pagy, or the built-in limit/offset, cover typical pagination.

Choosing columns

Ruby
Post.select(:id, :title)           # instantiates partial objects
Post.pluck(:title)                 # array of strings, no model objects
Post.pluck(:id, :title)            # array of pairs
Post.ids                           # primary keys only
Post.distinct.pluck(:status)

pluck skips building model objects and is much cheaper when you only need values. Reading a column you did not select raises ActiveModel::MissingAttributeError.

Ruby Stand-in: a lazy, chainable relation

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with gets), it gets the next line from this box.

The relation stayed lazy until pluck called to_a. In Rails the same laziness is why you can pass a half-built query from a controller to a view, and why a query is never run twice: after loading, the relation caches its records.

Counting and grouping

Ruby
Post.count                              # SELECT COUNT(*)
Post.where(published: true).count
Post.sum(:views)
Post.average(:views)
Post.maximum(:views)
Post.group(:status).count               # { "draft" => 3, "published" => 12 }
Post.group(:author_id).having("COUNT(*) > 5").count

Joins

Ruby
Post.joins(:comments)                           # INNER JOIN, duplicates if many comments
Post.joins(:comments).distinct
Post.left_joins(:comments).where(comments: { id: nil })   # posts with no comments
Post.joins(:author).where(users: { admin: true })

joins does not load the associated records. If you also want to read them, you need eager loading.

Scopes: name your queries

A scope is a named, chainable piece of query defined on the model:

Ruby
class Post < ApplicationRecord
  scope :published, -> { where(status: "published") }
  scope :recent,    -> { order(created_at: :desc) }
  scope :popular,   ->(min = 100) { where("views >= ?", min) }
end

Post.published.popular(500).recent.limit(5)

A scope always returns a relation, so it chains with everything else, even when the condition is nil (it returns all). This keeps controllers short and puts query knowledge in one place. Use a class method instead of a scope when the logic needs real branching, but make sure it returns a relation.

default_scope applies a condition to every query on the model. It is tempting (default_scope { where(deleted: false) }) but surprises people constantly, because it also affects create defaults and every join. Most teams avoid it.

Scopes on associations work too: author.posts.published.

Batches and big tables

Never run Post.all.each on millions of rows; it loads everything. Use batches:

Ruby
Post.find_each(batch_size: 1000) { |post| post.recalculate_score }
Post.in_batches(of: 500).update_all(archived: true)

update_all and delete_all run one SQL statement and skip validations and callbacks, which is fast but sharp.

Looking at the SQL

Post.published.to_sql returns the query text without running it, and Post.published.explain asks the database how it would run it. Rails also logs every query in log/development.log, which is the first place to look when a page is slow.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does a relation like Post.where(published: true) do until you read from it?

    Relations build up SQL and only execute when you iterate, count, pluck or otherwise need the data.
  2. 2. Which line is safe from SQL injection?

    Placeholders and hash conditions escape values. String interpolation and raw order input do not.
  3. 3. Why use pluck(:title) instead of map(&:title) on a relation?

    pluck selects just the column and returns plain values, saving memory and time.
  4. 4. What is true of a scope?

    Scopes return relations and can take arguments through a lambda.
  5. 5. Which method processes a huge table without loading every row at once?

    find_each loads records in batches of 1000 by default.

Summary

  • find, find_by, first, exists? fetch single records; where, order, limit build relations.
  • Relations are lazy and chainable; they run SQL only when read.
  • Use hash conditions or placeholders, never string interpolation; escape LIKE input.
  • pluck, select, count, sum, group shape the result.
  • joins/left_joins filter across tables; they do not load the other records.
  • Scopes name reusable queries; avoid default_scope.
  • Use find_each/in_batches for large tables.

Next: Eager loading and the N+1 problem