skip to content

Ordering, Plucking & Limits

order, limit, select and pluck decide which rows and columns come back, and whether you get models or plain arrays. Interviewers probe pluck versus select and what first or take return with no order.

part ofRuby on Railsread it →
on this pageshow

explore

questions

5

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%

answer

  1. models versus plain Ruby values
  2. select keeps a lazy relation
  3. pluck runs its query immediately
  4. MissingAttributeError on unselected columns
  5. pick is limit(1).pluck(...).first

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.

solid answer

~40 s

`Restaurant.select(:id, :name)` stays a lazy relation and, when loaded, builds `Restaurant` objects holding only `id` and `name`; reading `rating` on one raises `ActiveModel::MissingAttributeError`. `Restaurant.order(rating: :desc).limit(10).pluck(:name)` runs immediately and returns an array of strings, with several columns giving an array of arrays, and no models are instantiated, which is why it suits an autocomplete. `pick(:name)` is shorthand for `limit(1).pluck(:name).first`, so it returns one value (or one array) or `nil`, and it adds no ORDER BY of its own. `ids` plucks the primary key. Use `select` when you still need model behaviour (methods, associations, `to_param` for links); use `pluck` when you need raw values. Note that `pluck` replaces any earlier `select`, and that `select` with a block is Ruby's `Enumerable#select`, which loads every row.

code

ruby · 11 lines
ruby
class RestaurantSuggestionsController < ApplicationController
  def index
    names = Restaurant
      .where("name LIKE ?", "#{params[:q]}%")
      .order(rating: :desc)
      .limit(10)
      .pluck(:name)

    render json: names
  end
end

go deeper

for a junior

Know that pluck gives plain values and select gives model objects with only some columns, and use pluck for lists of names or ids.

for a middle

Explain laziness (select is a relation, pluck runs at once), MissingAttributeError on partial models, pick as limit(1).pluck.first, and pluck replacing select.

for a senior

Choose by memory and coupling: pluck for exports and autocomplete, partial select only when templates are known, and an explicit order before pick.

for a principal

Steer hot read paths away from instantiating models they do not need, and treat partial models as a contract between the query and every template it feeds.

## Two different questions Both methods decide **which columns** come back, but they answer different questions: - **`select`** says "build model objects, but only fetch these columns". It is a query method: it returns a new `ActiveRecord::Relation`, stays lazy, and can be chained further. - **`pluck`** says "I do not want models, give me the values". It is a terminal call: it runs the query right away and returns Ruby arrays. For the top-rated restaurants on a food-guide site: ```ruby Restaurant.order(rating: :desc).limit(10).select(:id, :name) # => relation; loads Restaurant objects with only id and name Restaurant.order(rating: :desc).limit(10).pluck(:name) # SELECT restaurants.name FROM restaurants ORDER BY rating DESC LIMIT 10 # => ["Chez Lou", "Basilico", ...] ``` ## select: partial models | Behaviour | Detail | |---|---| | return value | a relation; records are built when it is loaded | | missing columns | reading an unselected attribute raises `ActiveModel::MissingAttributeError` (the id is the exception: it reads as nil) | | aliases | `select("name AS label")` exposes `label` on each object | | further chaining | `where`, `order`, `limit` still apply | | block form | `select { \|r\| r.rating > 4 }` is `Enumerable#select`: it loads every row and filters in Ruby | Partial models are useful when a view needs model methods such as `to_param` for `link_to`, but not the wide text columns. The cost is fragility: a partial calling a method that touches an unselected column fails at render time, far from the query. ## pluck, pick and ids 1. **`pluck(:name)`** returns `["Chez Lou", ...]`; **`pluck(:id, :name)`** returns `[[1, "Chez Lou"], ...]`. Values are type-cast by the column type, so a decimal rating comes back as `BigDecimal`. 2. **`pick(:name)`** is `limit(1).pluck(:name).first`: one value, or `nil` when nothing matches. It does not add an order, so `Restaurant.pick(:name)` returns whichever row the database finds first; write `Restaurant.order(rating: :desc).pick(:name)` for "the best-rated name". 3. **`ids`** plucks the primary key: `Restaurant.where(city: "Lyon").ids`. Two behaviours surprise people: - `pluck` **replaces** an earlier `select`: `Restaurant.select(:name).pluck(:id)` selects only `id`. - On an **already loaded** relation, `pluck` of plain attribute names reads the loaded records instead of querying again. `pluck` also refuses an arbitrary SQL string that is not a column reference, raising `ActiveRecord::UnknownAttributeReference`; trusted expressions must be marked explicitly, which is its own topic. ## distinct with either `distinct` adds `SELECT DISTINCT`: - `Restaurant.distinct.pluck(:cuisine)` runs `SELECT DISTINCT cuisine` and returns each cuisine once, which is cheaper than `pluck(:cuisine).uniq` on a large table because duplicates never leave the database. - `Restaurant.select(:cuisine).distinct` does the same but returns partial `Restaurant` objects. - `distinct(false)` removes it again from a relation you were handed. ## Patterns to fix in code review | Seen in code | What it costs | Better | |---|---|---| | `Restaurant.where(...).map(&:name)` | one model per row, every column | `pluck(:name)` | | `Restaurant.where(...).select(:id).map(&:id)` | partial models just to read ids | `ids` | | `Restaurant.pluck(:name).first` | every name fetched for one value | `order(...).pick(:name)` | | `Restaurant.pluck(:cuisine).uniq` | duplicates shipped to Ruby | `distinct.pluck(:cuisine)` | `async_pluck`, `async_pick` and `async_ids` run the same queries in the background and return an `ActiveRecord::Promise`, useful when a controller can start several independent reads before rendering. ## Choosing | Need | Use | |---|---| | strings for an autocomplete or a CSV column | `pluck` | | one value from one row | `order(...).pick` | | a list of ids to pass on | `ids` | | objects with methods, links or associations | `select` (or no select at all) | | unique values of one column | `distinct.pluck(:column)` | Memory is the usual reason to choose `pluck`: building an Active Record object per row costs far more than an array of strings, which matters for exports and autocomplete endpoints that run on every keystroke.

  • Why might Restaurant.select(:id, :name) break a page that worked with the full model?
    The view or a helper reads an attribute that was not selected, such as `rating` in a partial or a method that uses `cuisine`, and Active Model raises `ActiveModel::MissingAttributeError` at render time. Either select every column the template touches or switch to `pluck` and render plain values.
  • How do you stop records loaded for a read-only page from being saved by mistake?
    Call `readonly` on the relation: `Restaurant.order(rating: :desc).limit(10).readonly`. Each loaded record then raises `ActiveRecord::ReadOnlyRecord` ("Restaurant is marked as readonly") on `save`, `update` or `destroy`. `readonly(false)` lifts it on a relation you were handed.
  • When does pluck not hit the database?
    When the relation is already loaded and every argument is a plain attribute name, `pluck` reads the values from the loaded records. It also returns an empty array without a query for a `none` relation or a known contradiction such as `where(id: [])`.

  • pluck returns Restaurant objects with only the plucked attributes set
  • select(:name) returns an array of name strings
  • pick(:name) returns the first restaurant by primary key
  • select(:name).pluck(:id) returns both name and id
  • select { |r| r.rating > 4 } filters in SQL
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, 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

On an Active Record model with random UUID primary keys, why does first return an arbitrary-looking record, and how do implicit_order_column and Rails 8.1 change that?

level: seniorimportance: nice to knowfreq 25%

basics

~20 s

first and last sort by the primary key when no order is given, and random UUIDs do not follow creation time. Setting implicit_order_column = "created_at" makes them sort by that column, with the primary key appended as a tiebreaker.

open as a page