skip to content

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%

answer

  1. models versus plain rows
  2. extra SELECT columns become readers
  3. count_by_sql ends in to_i
  4. Result: columns, rows, to_a
  5. select_value: first column of first row

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.

solid answer

~40 s

`Member.find_by_sql(sql)` runs the SQL as written and instantiates a `Member` per row, so any extra column such as `RANK() OVER (...) AS position` becomes a reader on each record; it returns a plain `Array`. `Member.count_by_sql(sql)` expects a `SELECT COUNT(*)` and returns `select_value(...).to_i`, an `Integer`. Below the model, a connection from `Member.with_connection { |c| ... }` gives `select_all` (an `ActiveRecord::Result` with `columns`, `rows` and `to_a` as hashes), `select_one` (one row hash or `nil`), `select_rows` (arrays of values), `select_values` (the first column) and `select_value` (the first column of the first row). The connection methods skip model instantiation, which makes them cheaper for a read-only chess-club ranking table, but the values are not cast through the model's attribute types.

code

ruby · 10 lines
ruby
top_rating = Member.with_connection do |conn|
  conn.select_value("SELECT MAX(rating) FROM members")
end

rated_games = Game.count_by_sql("SELECT COUNT(*) FROM games WHERE rated = TRUE")

pairings = Game.with_connection do |conn|
  conn.select_rows("SELECT white_id, black_id FROM games WHERE round = 3")
end
# => [[12, 40], [7, 19], ...]

go deeper

for a junior

Recall the return types: Array of models for find_by_sql, Integer for count_by_sql, ActiveRecord::Result for select_all, and that none of them chains like a relation.

for a middle

Explain how computed columns become readers, why unselected columns raise MissingAttributeError, and why select_* values skip the model's attribute types.

for a senior

Choose by cost: instantiating models for a two-column report wastes memory, while select_all rows lose every model method; show you know select_all hits the query cache and execute clears it.

for a principal

Frame raw reads as an exception path: decide where hand-written report SQL lives, how it is reviewed for binding, and when plain rows replace model instances.

## Two families of raw reads Active Record gives you two places to run a SELECT you wrote by hand. The **model-level** methods (`find_by_sql`, `count_by_sql` and their `async_` twins) live on the model class and hand back model objects or a number. The **connection-level** methods (`select_all`, `select_one`, `select_rows`, `select_values`, `select_value`) live on the database adapter and hand back plain data. Picking between them is mostly a question of what the caller needs: records with behaviour, or rows to print. ## find_by_sql and count_by_sql `find_by_sql(sql, binds = [], preparable: nil, allow_retry: false)` runs your statement through the connection's `select_all` (logged as `Member Load`) and then **instantiates** one model per row: - It returns an **`Array`**, even for one row, and an empty `Array` for none. It is not an `ActiveRecord::Relation`, so `where`, `order`, `limit` and `includes` cannot be chained after it. - Every column in the SELECT list becomes an attribute. Columns the table does not have, such as `position` or `games_played`, are readable as `member.position`; their types come from the result set's column types where the adapter reports them. - Columns you left out are **missing**, not `nil`: reading one raises `ActiveModel::MissingAttributeError`. - When the result includes the model's inheritance column (`type`), each row is instantiated as its single-table-inheritance subclass. - The SQL may be the array form, `["... WHERE club_id = :club_id", { club_id: club.id }]`, which is sanitized before it runs. `count_by_sql(sql)` calls `select_value` and then `to_i`, so it always returns an **`Integer`**, and a NULL becomes `0`. It is meant for a COUNT query too complex for `count` on a relation. Their twins `async_find_by_sql` and `async_count_by_sql` return an `ActiveRecord::Promise`; `value` blocks until the result arrives. ## The connection's select_* methods You reach the connection with `Member.with_connection { |conn| ... }`. Each method accepts a finished SQL string, an Arel node or a relation: | Method | Returns | Empty result | |---|---|---| | `select_all` | `ActiveRecord::Result` | an empty `Result` | | `select_one` | the first row as a `Hash` | `nil` | | `select_rows` | `Array` of value `Array`s | `[]` | | `select_values` | `Array` of the first column | `[]` | | `select_value` | the first column of the first row | `nil` | None of them instantiates a model, and none accepts the `[sql, values]` array form: build the string with `Member.sanitize_sql_array` first. Two details make them more useful than they look: - They accept a **relation**, so `conn.select_all(Member.where(club: club).select(:name, :rating))` keeps the query builder for the SQL while skipping instantiation for the result. - The second argument is a **name** written to the log beside the SQL, so `conn.select_all(sql, "Club ranking")` makes the report easy to find in the development log. `select_all` also goes through the **query cache** inside a request, while `execute` and `exec_query` are treated as writes that clear it. ## ActiveRecord::Result `ActiveRecord::Result` is an `Enumerable` wrapper around the raw result: 1. `columns` is the list of column names, `rows` the list of value arrays. 2. `to_a` (also `to_ary`) builds an `Array` of `Hash`es keyed by column name; `each` yields read-only hash-like rows. 3. `length`, `empty?`, `includes_column?` and `column_types` describe it; `column_types` may be empty on adapters that do not report types. The values are whatever the database driver produced. They do **not** pass through the model's attribute API, so an `enum` column stays its stored integer and a serialized column stays its stored text. ## Choosing one - Need records with methods, validations or associations, plus a computed column or two? `find_by_sql`. - Need one number from a hand-written COUNT? `count_by_sql`. - Need rows for a report, a CSV export or a JSON payload, with no model behaviour? `select_all`, or `select_rows` when column names do not matter. - Need a single scalar such as `MAX(rating)`? `select_value`. ```ruby ranking = Member.find_by_sql([<<~SQL, { club_id: club.id }]) SELECT members.*, RANK() OVER (ORDER BY rating DESC) AS position FROM members WHERE club_id = :club_id SQL ranking.first.position # computed column, read like an attribute rows = Member.with_connection do |conn| conn.select_all("SELECT name, rating FROM members ORDER BY rating DESC LIMIT 10") end rows.columns # => ["name", "rating"] rows.to_a # => [{"name" => "Ilse", "rating" => 2310}, ...] ``` The trade is cost against convenience: instantiating thousands of models costs memory and time that a report printing two columns does not need, while plain rows give up every model method.

  • Why might ranking.first.save fail or misbehave on a record loaded with find_by_sql that selected only id and name?
    The record is a normal `Member`, but every unselected column is missing, so any validation or callback that reads one raises `ActiveModel::MissingAttributeError`. Select `members.*` plus the computed columns when the records will be written back, or treat them as read-only data.
  • How does select_all differ from execute for a read in a Rails request?
    `select_all` returns an `ActiveRecord::Result` and goes through the query cache. `execute` returns the driver's raw result and is treated as a possible write, so it clears the query cache. For reads, prefer `select_all` or one of its narrower siblings.

  • find_by_sql returns a relation you can keep chaining where and order onto.
  • Columns missing from a find_by_sql SELECT simply read as nil.
  • count_by_sql returns an ActiveRecord::Result holding one count row.
  • select_all instantiates model objects just like find_by_sql.
  • Values from select_all are cast through the model's enum and serialize declarations.
  • connection.select_all accepts the [sql, values] array form and sanitizes it.