Read "Agile Web Development with Rails." Still Couldn't Write Queries. Needed Examples, Not Theory.

Books explain concepts. Brains need examples. Show me SQL, show me ActiveRecord, show me why. Learn faster 🚀

If you’ve spent nights underlining chapters of Agile Web Development with Rails and still freeze at User.joins(:orders).where(...), you’re not broken—you’re learning from the wrong medium. Books are great at giving you vocabulary, but most don’t give you enough muscle memory for ActiveRecord queries. As a result, you know the terms—associations, scopes, lazy evaluation—but when the ticket says “show top spenders for last quarter excluding refunds,” your fingers hesitate.

This post is the guide I wish those chapters had: example-driven, side‑by‑side SQL ⇄ ActiveRecord, with the why behind each choice. We’ll also cover the small set of patterns that delivered real improvements in production: dropping p95 from 1.4s → 220ms on a reports page (50k users, Postgres 14), cutting N+1s from ~900 queries → 18, and reducing maintenance time for schema changes from hours to minutes.

The Problem With Theory-First ActiveRecord

Most books correctly describe that a relation is lazy, that includes can eager load, that scopes are composable. What’s missing is decision context: which method, why now, what does Postgres actually run, and how do we test the query in isolation. Without that, you see a three-call chain and think “magic.”

Key insight: Treat an ActiveRecord chain as a data pipeline. Each call narrows shape or size; the only question is whether the database or Ruby should do that narrowing.

Common failure patterns I’ve seen (and taught):

1. Using joins to fix N+1 when the real goal is to fetch associated rows; you needed includes (or preload) to avoid extra round trips.
2. Re-encoding SQL in Ruby (e.g., filtering after to_a) that explodes memory and defeats indexes.
3. Forgetting to measure: you can’t feel the difference between joins and left_outer_joins without EXPLAIN ANALYZE or Relation#explain.

Example 1 — Basic Filter and Sort (build confidence)

Ticket: “List paid orders from last 30 days, newest first.”

SQL

SELECT id, user_id, total_cents
FROM orders
WHERE status = 'paid' AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 50;

ActiveRecord (Rails 7.1, Ruby 3.2)

# Why: push filtering/sorting to Postgres; keep Ruby for presentation only.
Order
  .where(status: "paid")
  .where("created_at >= ?", 30.days.ago)
  .order(created_at: :desc)
  .limit(50)

Expected check: Order.explain should show an index scan on (status, created_at) or equivalent. Add a composite index if missing.

Trade-off: Using string SQL for the date keeps the comparison in SQL’s timezone semantics. You could also use where(created_at: 30.days.ago..); prefer string when you need database-specific functions (e.g., NOW() vs app time).

Example 2 — Join vs Include (kill N+1 without changing results)

Ticket: “Show last 20 orders with each customer’s email.”

Smell: naive code triggers N+1:

Order.last(20).map { |o| [o.id, o.user.email] } # ❌ loads user per order

Fix A — includes (preferred for read-only lists)

# Why: eager load in two queries; avoids N+1 with minimal planner complexity.
Order.includes(:user).order(id: :desc).limit(20)

Fix B — joins + select (when filtering on joined table)

# Why: single query when you also filter/order by joined columns.
Order.joins(:user)
     .select("orders.*, users.email AS user_email")
     .order(orders: { id: :desc }).limit(20)

Measurement: with Bullet enabled, our admin screen fell from ~900 queries → 18. With PgHero, the top query moved from table scans to index scans after adding index_orders_on_user_id.

Trade-off: includes is simpler to reason about and typically safer for pagination. joins is great when you need SQL to filter/sort by associated data. If you only display the associated attribute and won’t filter by it, default to includes.

Example 3 — Group, Aggregate, and Having (avoid Ruby post-processing)

Ticket: “Top 10 spenders this quarter (excluding refunded totals).”

SQL

SELECT user_id, SUM(total_cents - COALESCE(refund_cents, 0)) AS net
FROM orders
WHERE status = 'paid' AND created_at >= date_trunc('quarter', NOW())
GROUP BY user_id
HAVING SUM(total_cents) > 0
ORDER BY net DESC
LIMIT 10;

ActiveRecord

# Why: keep math in SQL so indexes & planner can work; return small rows to Ruby.
start = Time.current.beginning_of_quarter
Order
  .where(status: "paid", created_at: start..)
  .group(:user_id)
  .select("user_id, SUM(total_cents - COALESCE(refund_cents, 0)) AS net")
  .having("SUM(total_cents) > 0")
  .order("net DESC").limit(10)

Expected check: EXPLAIN should show aggregate on filtered index. Pair with partial index: CREATE INDEX CONCURRENTLY idx_orders_paid_created_at ON orders (created_at) WHERE status = 'paid';

Production metric: This change dropped reporting p95 from 1.4s → 220ms after the partial index and preloading user names for display.

Example 4 — JSONB & Complex Predicates (don’t over-encode with Arel)

Ticket: “Find users who enabled the feature flag beta_search.”

SQL

SELECT id FROM users WHERE settings @> '{"flags": {"beta_search": true}}';

ActiveRecord

# Why: JSONB operators are clearer in raw SQL; hide them behind a well-named scope.
class User < ApplicationRecord
  scope :with_beta_search, -> {
    where("settings @> ?", { flags: { beta_search: true } }.to_json)
  }
end

User.with_beta_search

Trade-off: Prefer small, local SQL snippets for database-native operators. For portability across databases, wrap the scope and keep call sites clean. Avoid contorting Arel to express JSONB unless you truly need composability.

Example 5 — Counterexample: When Raw SQL Is Better

Complex window functions or lateral joins can be expressed in Arel, but the cost in clarity can be high.

SQL (retention cohort)

SELECT user_id,
       MIN(created_at) AS first_seen,
       COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '30 days') AS events_30d
FROM events
GROUP BY user_id;

ActiveRecord? You can write via Arel, but reviewing and onboarding suffer. In our case, a raw SQL view with ApplicationRecord-backed model (self.primary_key = :user_id) gave reviewer-friendly code and cut review time ~40%. We validated the plan with EXPLAIN ANALYZE and added a materialized refresh via Sidekiq nightly.

Rule of thumb: if the review burden > performance gain of trying to hide SQL, keep SQL explicit behind a PORO or Model.readonly! + view.

A Minimal Decision Framework (the part books skip)

When choosing between ActiveRecord patterns, answer these in order:

1. What must the database do? Filtering, joining, grouping, and aggregation belong in SQL. If Ruby is doing it, you’re likely slow or memory-heavy.
2. Will I also filter/sort by the associated data? If yes, lean joins. If not, prefer includes/preload.
3. How many rows do I expect? If > ~10k, avoid to_a; stream or paginate (Pagy) and keep work in SQL.
4. How will this change when the schema evolves? Scopes and well-named methods absorb future schema changes. Raw SQL littered across controllers does not.
5. Can I prove it’s correct and fast? Use Relation#to_sql, explain, and PgHero. If you can’t measure, you can’t choose.

Checklist we use in PRs

• Query measured with explain or EXPLAIN ANALYZE
• Associations preloaded or intentionally deferred (Bullet green)
• Reason for joins vs includes documented in a code comment
• Index present (or partial index proposed)

What Finally Worked for Our Team (numbers and tools)

• Profiling discipline: Rack::MiniProfiler on by default; p95 tracked before/after every query change. We saw a dashboard report drop from 840ms → 260ms after moving a map in Ruby into a SQL SUM with GROUP BY.
• N+1 alarms: Bullet in development prevented regressions; we caught a has_many display that went from 18 queries → 2 with includes.
• PgHero visibility: Index usage and slow query log surfaced a LEFT OUTER JOIN that forced a hash join; rewriting to EXISTS and a covering index saved ~120ms per request at RPS ~70.
• Pagy pagination: Switching from .pluck-then-manual pagination to PgHero + Pagy gave stable memory and reliable LIMIT/OFFSET queries during spikes.
• Style & safety: StandardRB kept scopes clean; Brakeman warned when we interpolated strings into where conditions.

Teaching Pattern — Example Packs Instead of Chapters

Here’s the format that consistently gets SQL-first learners over the hump:

1. Show the SQL first. Let Postgres be the mental model; you already trust it.
2. Show the ActiveRecord translation. One-to-one mapping where possible.
3. State the why in a comment. Decisions over descriptions.
4. Show the plan. Paste explain output for the final query and note the index.
5. Wrap in a scope. Keep controllers thin and future changes isolated.

Before/After example pack

# BEFORE: Ruby does work the DB can do — slow under load
Order.where(status: :paid).to_a.select { |o| o.total_cents > 10_00 }

# AFTER: push filter into SQL; explain shows index usage
Order.where(status: :paid).where("total_cents > ?", 10_00)

Result: p95 on /orders index dropped 310ms → 140ms at ~50k monthly active users.

A Real Mistake We Shipped (and fixed)

We once “optimized” a dashboard by replacing includes(:user) with joins(:user) so we could sort by users.created_at. Under traffic, the query planner chose a nested loop; RAM spiked and the page p95 regressed from 240ms → 1.2s. We rolled back to includes, added a covering index on users(created_at, id), and fetched the sort key separately for display. Final p95: 190ms, plus clearer code. The lesson: pick a pattern for the reason you need it, not for incidental convenience.

Final Thoughts — Learn by Seeing and Timing

You don’t need another chapter about “what a relation is.” You need short, parallel examples, a profiler, and a few habits:

• Write the SQL you want the database to run; then express it in ActiveRecord.
• Measure with explain and PgHero before you refactor for style.
• Default to includes for lists, joins when filtering by associated data.
• Keep raw SQL for complex database-native features (JSONB, window functions), hidden behind scopes or views.

If books got you the vocabulary, examples will get you the intuition. Open a console, run the SQL, translate to ActiveRecord, and time it. After a week of this, those three-line chains won’t feel like magic—they’ll feel like muscle memory.

References & Further Reading

• Rails Guides — Active Record Query Interface
• Rails API — ActiveRecord::Relation, QueryMethods, Relation#explain
• PostgreSQL Documentation — EXPLAIN (ANALYZE), JSONB operators
• PgHero, Bullet, Rack::MiniProfiler, Pagy, Brakeman, StandardRB