skip to content

Query Interface

Active Record relations build SQL lazily from chained calls such as where, joins, includes and pluck. Interviewers probe whether you can predict the SQL a chain runs and how many queries it takes.

part ofRuby on Railsread it →
on this pageshow

explore

questions

page 1 of 2

In Active Record, why use find_each instead of Member.all.each to re-score a million loyalty members, and how does it page through the table?

level: juniorimportance: must knowfreq 62%

answer

  1. bounded memory, many queries
  2. batch_size defaults to 1000
  3. ORDER BY id with id > last
  4. scoped order is ignored
  5. find_in_batches yields Arrays

basics

~20 s

Member.all.each loads every row into memory before the loop starts; find_each loads 1,000 members at a time in primary-key order, continuing with WHERE id > the last id seen, so memory stays flat however large the table grows.

solid answer

~40 s

`Member.all.each` materialises the whole relation, a million `Member` objects, before yielding the first one. `find_each` runs `ORDER BY id LIMIT 1000`, yields those records one by one, then asks for the next 1,000 with `id >` the last id it saw, until a batch comes back short. Memory is bounded by `batch_size` (default 1000), at the cost of one query per batch. It works on any relation, `Member.where(active: true).find_each`, but it forces its own order: a scoped `order` is ignored with a logged warning, or raises `ArgumentError` when `config.active_record.error_on_ignored_order` is true. `find_in_batches` uses the same walk and yields each batch as an `Array`, which suits bulk work such as building one API request per batch. Both skip the query cache, so finished batches are not kept around.

code

ruby · 12 lines
ruby
# Loads every active member before the first iteration
Member.where(active: true).each { |m| m.update!(loyalty_score: LoyaltyScore.for(m)) }

# 1,000 members in memory at a time, walked by id
Member.where(active: true).find_each do |member|
  member.update!(loyalty_score: LoyaltyScore.for(member))
end

# One Array per batch, for per-batch work
Member.where(active: true).find_in_batches(batch_size: 500) do |members|
  RewardsApi.sync(members.map(&:reward_payload))
end

go deeper

for a junior

Recall that all.each loads everything, that find_each loads 1,000 at a time by primary key, and that find_in_batches yields Arrays.

for a middle

Explain the id > last_id walk, why a scoped order is ignored or raises, and why batches skip the query cache.

for a senior

Show that batching bounds memory, not write count, plan resumption with start:, and know that batches see no single snapshot of a live table.

for a principal

Decide when a backfill belongs in a resumable job, a batched SQL statement or a one-off script, weighing memory, run time and restart cost.

## The problem with all.each `Member.all.each` reads like a stream but is not one. Iterating an `ActiveRecord::Relation` **loads** it: Active Record runs one `SELECT`, instantiates every row as a model and keeps the whole `Array` until the loop ends. For a million loyalty members that is a million objects, each with its attribute set, alive at once. The process grows by hundreds of megabytes or more, the garbage collector works harder, and a container with a memory limit may be killed halfway through. ## How find_each walks the table `find_each` replaces one huge read with many small ones. By default it: 1. Runs the relation with `ORDER BY members.id ASC LIMIT 1000`. 2. Yields each of those records to the block, one at a time. 3. Remembers the **last id** of the batch and runs the next query with `members.id > last_id`. 4. Stops when a batch returns fewer rows than the batch size. ```sql SELECT members.* FROM members WHERE members.active = TRUE ORDER BY members.id ASC LIMIT 1000 SELECT members.* FROM members WHERE members.active = TRUE AND members.id > 1874 ORDER BY members.id ASC LIMIT 1000 ``` Because each query starts **after the last id** instead of using `OFFSET`, a late batch costs about as much as an early one when the cursor column is indexed. Rows from a finished batch are no longer referenced by Active Record and can be garbage-collected, and the batch queries **skip the query cache**, which would otherwise keep every batch in memory for the rest of the request. ## The rules that come with it - **Batch size**: `batch_size:` defaults to **1000**. Larger batches mean fewer queries and more memory per batch. - **Order is forced**: the walk needs its own `ORDER BY`. A relation that already has one, `Member.order(:name).find_each`, has that order **ignored**, and Rails logs "Scoped order is ignored, use :cursor with :order to configure custom order." With `config.active_record.error_on_ignored_order = true` (default `false`), or `error_on_ignore: true` on the call, it raises `ArgumentError` instead. - **Limits are honoured**: `Member.limit(5_000).find_each` stops after 5,000 rows. - **No block, an Enumerator**: `Member.find_each.with_index { |member, i| ... }` works. - **Not a database cursor**: Active Record runs ordinary `LIMIT` queries; it does not hold a server-side cursor or a transaction open across batches. ## find_each versus find_in_batches | Method | Yields | Use it for | |---|---|---| | `find_each` | one record at a time | per-record work: recalculating a score, sending a mailer | | `find_in_batches` | an `Array` of up to `batch_size` records | per-batch work: one bulk API call, one `upsert_all` per batch | | `Member.all.each` | one record at a time, from a fully loaded `Array` | small sets that fit comfortably in memory | The guide's advice holds: for a loop over a few hundred records the ordinary finders are simpler, and batching only pays off when the set would not fit in memory. ## Re-scoring a million members ```ruby Member.where(active: true).find_each(batch_size: 2_000) do |member| member.update!(loyalty_score: LoyaltyScore.for(member)) end ``` Each `update!` still runs validations and callbacks and writes one row, so this is a **memory** fix, not a speed fix: a million members still means a million `UPDATE`s. When the new score can be computed in SQL, `in_batches` with `update_all` writes a whole batch per statement. Associations read inside the block need `includes` on the relation, which is applied to each batch, or the loop turns into an N+1 per member. ## Inside the block - Each `update!` runs in its **own transaction**; find_each does not wrap the loop in one, so a crash at member 412,000 leaves the first 411,999 changes committed. - An exception raised in the block **stops the walk**. Rescue and record per-record failures if one bad member should not halt the run. - Keep the block's work proportional: a network call per member turns a memory problem into a run-time one, which is when `find_in_batches` and one call per batch pay off. ## What batching does not give you Batches are separate queries, so they do **not** see one consistent snapshot: rows inserted behind the cursor are missed, and rows inserted ahead of it are picked up. A job that dies halfway must be restarted with `start:` set to the last id it finished, or it begins again from the first row.

  • Why does find_each continue with id > last_id instead of OFFSET?
    An `OFFSET` makes the database read and discard every earlier row, so batch 900 costs far more than batch 1. Continuing after the last id uses the primary-key index to jump straight to the next batch, and rows deleted between batches do not shift later rows out of view.
  • How do you resume a find_each job that crashed after member 412,000?
    Pass `start: 412_001`; `start:` is inclusive, so starting at the last finished id would repeat that one row. Record the last processed id somewhere durable as the loop runs, or make the per-record work safe to repeat so an overlap does no harm.

Unloading a delivery truck one pallet at a time instead of tipping the whole load onto the shop floor: the floor never fills up, but you make many more trips to the truck.

  • Member.all.each streams rows from the database one at a time.
  • find_each keeps the scope's order(:name) and batches in name order.
  • find_each pages with OFFSET, so late batches cost the same as early ones.
  • find_each holds a server-side cursor and a transaction open across batches.
  • find_each makes per-record updates fast because it batches the writes too.
open as a page

In Active Record, what SQL does where generate when a hash condition's value is nil, an array, or a Ruby range?

level: juniorimportance: must knowfreq 72%

basics

~20 s

The value's type picks the operator: a scalar gives =, nil gives IS NULL, an array gives IN (...), and a range gives BETWEEN, or >=, < and <= comparisons for exclusive and open-ended ranges. Keys are ANDed together.

open as a page

In Active Record, which queries does Order.includes(:customer, line_items: :wine) run for an orders page printing customers, items and wines?

level: juniorimportance: must knowfreq 82%

basics

~20 s

With no conditions on the associations, it runs four queries in total: one for the orders, then one IN-list query each for customers, line items and wines, so order.customer and line_item.wine read from memory instead of querying per row.

open as a page

In Active Record, what SQL do joins and left_joins generate from association names, and how do you filter on the joined table?

level: juniorimportance: must knowfreq 74%

basics

~20 s

joins(:appointments) adds an INNER JOIN built from the association, so only doctors with appointments remain; left_joins(:appointments) adds a LEFT OUTER JOIN and keeps every doctor. Filter on the joined table with where(appointments: { starts_at: range }).

open as a page

In Active Record, when does Article.published.featured.order(:published_at) actually query the database, and what do load, loaded? and reset do?

level: juniorimportance: must knowfreq 60%

basics

~20 s

Building the chain runs no SQL; the SELECT runs when records are first needed, such as on each or to_a. load runs it now and returns the relation, loaded? reports cached records, and reset discards them.

open as a page

In Active Record, what is the difference between select and pluck, and when would you reach for pick or ids instead?

level: juniorimportance: must knowfreq 70%

basics

~20 s

select narrows the SELECT list but still returns model objects with only those attributes; pluck runs the query at once and returns plain values. pick returns the first row's values, and ids returns the primary keys.

open as a page

In Active Record, how do count, size and length differ on a relation and on a has_many association, and what query does each run?

level: middleimportance: must knowfreq 72%

basics

~20 s

count runs SELECT COUNT() even when the records are already loaded; length loads every record, then counts the Ruby array; size reuses loaded records when they exist and otherwise runs COUNT(), so size is the safe default when you do not know the load state.

open as a page

In Active Record, what does Sale.group(:store_id).sum(:amount) return, and how do having and regroup change a grouped calculation?

level: middleimportance: must knowfreq 58%

basics

~20 s

A grouped calculation returns a Hash keyed by the grouped value, here each store_id mapped to its total; having filters whole groups by an aggregate after grouping, and regroup replaces the existing GROUP BY instead of appending to it.

open as a page

In Active Record, how do insert_all, insert_all! and upsert_all differ for importing a nightly grocery price feed, and what do they skip compared with create?

level: middleimportance: must knowfreq 52%

basics

~20 s

All three write an array of hashes in one INSERT without instantiating models or running validations and callbacks. insert_all skips rows that hit a unique index, insert_all! raises ActiveRecord::RecordNotUnique and inserts nothing, and upsert_all updates the existing row instead.

open as a page

In an Active Record where string, how do positional ? and :named placeholders bind their values, and how do they handle arrays?

level: middleimportance: must knowfreq 66%

basics

~20 s

Each ? takes the next extra argument and each :name takes the matching key of a trailing hash. Rails binds and quotes every value, expands an array into a comma list, and raises ActiveRecord::PreparedStatementInvalid when the counts do not match.

open as a page

In Active Record, why does Doctor.joins(:appointments).where(appointments: { starts_at: week }) list a doctor several times, and how do you return each doctor once?

level: middleimportance: must knowfreq 62%

basics

~10 s

An INNER JOIN produces one row per matching appointment, and Active Record builds one Doctor object per row. Add .distinct, or filter with where(id: Appointment.where(...).select(:doctor_id)) so the doctors table is never multiplied.

open as a page

In Active Record, how do you bind user input into hand-written SQL with the array form, sanitize_sql_array and sanitize_sql_like?

level: middleimportance: must knowfreq 55%

basics

~20 s

Pass [sql, values] with ? or :name placeholders to find_by_sql or count_by_sql; use Member.sanitize_sql_array to get the quoted string where only a string is accepted, and wrap search text in sanitize_sql_like so % and _ match literally.

open as a page

In an Active Record model, how does a lambda scope differ from a class method returning a relation, especially when the condition is nil?

level: middleimportance: must knowfreq 66%

basics

~20 s

A scope is a class method generated from a lambda: if the lambda returns nil or false, the scope returns all instead, so chains keep working, while a hand-written class method returns exactly what its body returns, nil included.

open as a page

In Active Record, how do first, last, take and sole differ in the SQL they run and in what they return when nothing matches?

level: middleimportance: must knowfreq 58%

basics

~20 s

On an unordered relation, first sorts by the primary key (or implicit_order_column) ascending with LIMIT 1, last reverses that order, and take adds only LIMIT 1. All three return nil when nothing matches; sole raises unless exactly one row matches.

open as a page

In Active Record, how do includes, preload and eager_load differ, and when does includes switch to a single LEFT OUTER JOIN?

level: seniorimportance: must knowfreq 62%

basics

~20 s

preload always runs a separate IN-list query per association; eager_load always loads through a LEFT OUTER JOIN; includes behaves like preload until a where hash, order, references call or matching joins needs the associated table.

open as a page

In Active Record, what do sum, average, minimum and maximum return on a relation, and what comes back when no rows match?

level: juniorimportance: should knowfreq 48%

basics

~20 s

Called with a column, each runs one SQL aggregate and returns a single cast value: sum returns 0 when nothing matches, average returns a BigDecimal for integer and decimal columns, and average, minimum and maximum return nil on an empty set.

open as a page

In Active Record, why use exists? instead of present? to check whether any apartment matches a filter, and what SQL runs?

level: juniorimportance: should knowfreq 52%

basics

~20 s

exists? runs SELECT 1 AS one ... LIMIT 1 and returns true or false without building models, while present? loads every matching row into memory first. Use exists? when only the yes or no matters.

open as a page

In Active Record, what do find_by_sql, count_by_sql and a connection's select_all, select_rows and select_value each return?

level: juniorimportance: should knowfreq 42%

basics

~20 s

find_by_sql returns an Array of model instances built from any SELECT, count_by_sql returns an Integer, and a connection's select_all returns an ActiveRecord::Result, select_rows an Array of value arrays and select_value one scalar. None returns a chainable relation.

open as a page

In Active Record, what SQL do count, count(:coupon_code), distinct.count(:store_id) and Store.joins(:sales).count run, and why do their numbers differ?

level: middleimportance: should knowfreq 45%

basics

~10 s

count and count(:all) run COUNT(*); count(:column) runs COUNT(column) and skips NULLs; distinct.count(:column) runs COUNT(DISTINCT column); and a join counts joined rows, so Store.joins(:sales).count returns sales rows, not stores.

open as a page

In Active Record, how does in_batches differ from find_each, and when does in_batches with update_all beat loading each record?

level: middleimportance: should knowfreq 45%

basics

~20 s

in_batches yields an unloaded relation per batch of 1,000 ids instead of model objects, so batch.update_all writes a whole batch in one UPDATE. That beats find_each when the change is expressible in SQL and per-record validations and callbacks are not needed.

open as a page

In Active Record, how do where.missing and where.associated find apartments without photos or with photos, and what SQL do they run?

level: middleimportance: should knowfreq 38%

basics

~10 s

Apartment.where.missing(:photos) left-joins photos and keeps rows where photos.id IS NULL; Apartment.where.associated(:photos) inner-joins photos and keeps rows where photos.id IS NOT NULL. Both take association names and accept several at once.

open as a page

In Active Record, why does Apartment.where.not(pets_policy: "none") leave out apartments whose pets_policy is NULL, and how do you include them?

level: middleimportance: should knowfreq 50%

basics

~20 s

where.not writes pets_policy != 'none', and for a NULL row that comparison is unknown rather than true, so SQL drops the row. Add the NULLs back with .or(Apartment.where(pets_policy: nil)), or make the column NOT NULL.

open as a page

With the Bullet gem in a Rails app, what three problems does it detect, and how do you make it fail the test suite?

level: middleimportance: should knowfreq 45%

basics

~10 s

Bullet watches association access at runtime and reports N+1 queries, unused eager loading and missing counter caches; setting Bullet.raise = true in the test environment makes a detection raise Bullet::Notification::UnoptimizedQueryError and fail the test.

open as a page

In Active Record, why does Order.includes(:line_items).where(line_items: { backordered: true }) leave each order holding only its backordered line items?

level: middleimportance: should knowfreq 42%

basics

~20 s

The where hash makes includes eager load through one LEFT OUTER JOIN, and Active Record builds each order's line_items from the joined rows that passed the filter, so the association is marked loaded holding only the backordered items.

open as a page

In Active Record, when is joins the right choice over includes, and what happens if a view reads doctor.appointments after a joins query?

level: middleimportance: should knowfreq 55%

basics

~20 s

Use joins when appointments only decide which doctors come back or how they sort; it loads no Appointment objects. Reading doctor.appointments afterwards runs one more query per doctor, which is when includes is the right call.

open as a page

In Active Record, how does merge let Doctor.joins(:appointments) reuse an Appointment scope, and what can go wrong when you merge?

level: middleimportance: should knowfreq 45%

basics

~10 s

Doctor.joins(:appointments).merge(Appointment.confirmed) copies the Appointment scope's conditions, still bound to the appointments table, into the doctor query. The join must already be there, and on the same column merge's conditions replace yours.

open as a page

In an Active Record model, why is default_scope considered risky, and how do unscoped, unscope and all_queries: change what it reaches?

level: middleimportance: should knowfreq 52%

basics

~20 s

default_scope silently adds its conditions to every query, association and new record of the model; unscoped removes all scoping including an association's foreign key, unscope removes one named part, and all_queries: true extends the scope's where clauses to record updates and deletes.

open as a page

In Active Record, why return Article.none rather than [] or nil from a method that callers chain onto, and what SQL does it run?

level: middleimportance: should knowfreq 35%

basics

~20 s

Article.none returns a chainable relation that is known to be empty, so callers can keep adding scopes; loading, counting or checking it returns [], 0 or false without sending any SQL, whereas [] and nil break the chain.

open as a page

In Active Record, why can Restaurant.order(rating: :desc).limit(10).offset(10) show the same restaurant on two pages, and how do you fix it?

level: middleimportance: should knowfreq 40%

basics

~10 s

Many restaurants share a rating, and SQL leaves the order of tied rows undefined, so each LIMIT/OFFSET query may break ties differently. Add a unique tiebreaker: order(rating: :desc, id: :desc).

open as a page

In Active Record, how do order, reorder, reverse_order and in_order_of differ when sorting a list of restaurants?

level: middleimportance: should knowfreq 45%

basics

~10 s

order appends sort keys to any already present, reorder replaces them, reverse_order flips every direction, and in_order_of sorts (and by default filters) rows by an explicit list of values using a CASE expression.

open as a page

showing 1–30 of 41