RoR Querying with ActiveRecord

ActiveRecord gives you a full toolkit for fetching data from your database using Ruby. You never need to write raw SQL for common operations. Every query method returns either a single record, an array of records, or an ActiveRecord::Relation object that you can chain with more conditions.

Basic Finders

Product.all                         # Every product
Product.first                       # First record by id
Product.last                        # Last record by id
Product.find(5)                     # Product with id = 5 (raises error if missing)
Product.find_by(name: "Widget")     # First match (returns nil if missing)
Product.find_by!(name: "Widget")    # Same but raises error if missing
Product.count                       # Total number of products

Filtering with where

Product.where(available: true)
Product.where(category: "electronics")
Product.where("price > ?", 50)
Product.where("price BETWEEN ? AND ?", 10, 100)
Product.where(category: ["books", "electronics"])    # IN clause
Product.where.not(status: "archived")
Product.where("name LIKE ?", "%phone%")

Always use ? placeholders instead of string interpolation. This prevents SQL injection:

SAFE:   Product.where("name = ?", params[:name])
UNSAFE: Product.where("name = '#{params[:name]}'")   ← never do this

Ordering Results

Product.order(:name)                        # A to Z
Product.order(name: :asc)                   # Same
Product.order(name: :desc)                  # Z to A
Product.order(price: :asc, name: :asc)      # Sort by price, then name
Product.order("created_at DESC")            # Raw SQL string

Limiting and Offsetting

Product.limit(10)                           # First 10 records
Product.offset(20)                          # Skip first 20
Product.limit(10).offset(20)                # Records 21–30 (page 3 of 10)

Chaining Queries

Query methods return an ActiveRecord::Relation, which means you chain them together. Rails builds one SQL query and runs it only when the data is actually needed.

Product
  .where(available: true)
  .where("price < ?", 100)
  .order(price: :asc)
  .limit(5)

Generated SQL:
SELECT * FROM products
WHERE available = true AND price < 100
ORDER BY price ASC
LIMIT 5

This lazy evaluation is efficient — Rails waits until you actually use the data (loop through it, call .to_a, etc.) before hitting the database.

Selecting Specific Columns

Product.select(:id, :name, :price)
Product.select("name, price")
Product.pluck(:name)             # Returns array of names only: ["Widget", "Gadget"]
Product.pluck(:id, :name)        # Returns array of pairs: [[1, "Widget"], [2, "Gadget"]]

pluck is faster than select because it skips building Ruby objects and returns raw values directly.

Aggregation Methods

Product.count                              # Total count
Product.count(:category)                   # Count of non-nil categories
Product.sum(:price)                        # Sum of all prices
Product.average(:price)                    # Average price
Product.minimum(:price)                    # Lowest price
Product.maximum(:price)                    # Highest price

Product.where(available: true).average(:price)  # Average price of available products

Grouping Results

Product.group(:category).count
# => { "electronics" => 12, "books" => 8, "clothing" => 20 }

Product.group(:category).sum(:price)
# => { "electronics" => 15000, "books" => 400 }

Product.group(:available).count
# => { true => 35, false => 5 }

Joining Tables

# INNER JOIN — only posts that have a user
Post.joins(:user)

# Filter joined data
Post.joins(:user).where(users: { active: true })

# LEFT OUTER JOIN — all posts, even those without a user
Post.left_outer_joins(:user)

Eager Loading — Fix N+1 Queries

When you display posts and their authors, a common mistake causes one query per post (the N+1 problem). Eager loading fixes it with one extra query upfront.

BAD (N+1 problem):
@posts = Post.all
@posts.each do |post|
  puts post.user.name    ← runs a separate SQL query per post
end
# 1 query for posts + 1 query per post = N+1 queries

GOOD (eager loading):
@posts = Post.includes(:user).all
@posts.each do |post|
  puts post.user.name    ← no extra query; data already loaded
end
# 2 queries total regardless of how many posts exist

Query Diagram

Your Code
  Product.where(available: true).order(:price).limit(10)
        |
        v
ActiveRecord::Relation built (lazy — no DB hit yet)
        |
        v
You loop through it in a view or call .to_a
        |
        v
ActiveRecord sends SQL to the database:
  SELECT * FROM products WHERE available = TRUE ORDER BY price ASC LIMIT 10
        |
        v
Database returns rows
        |
        v
ActiveRecord maps rows to Product objects
        |
        v
Your code has 10 Product objects to work with

Scopes for Reusable Queries

class Product < ApplicationRecord
  scope :available,    -> { where(available: true) }
  scope :affordable,   -> { where("price < 50") }
  scope :by_category,  -> (cat) { where(category: cat) }
  scope :recent,       -> { order(created_at: :desc).limit(10) }
end

# Use them anywhere
Product.available
Product.available.affordable
Product.by_category("books").recent

Existence Checks

Product.exists?(id: 5)               # Does a product with id=5 exist?
Product.exists?(name: "Widget")      # Does a product named Widget exist?
user.posts.exists?                   # Does this user have any posts?
user.posts.any?                      # Same result
user.posts.empty?                    # Opposite
user.posts.none?                     # Same as empty?

Mastering ActiveRecord queries lets you fetch exactly the data you need efficiently. Combine scopes, filters, ordering, and eager loading to write clean, fast, and readable data access code.