ActiveRecord select_* cheat sheet

You are familiar with ActiveRecord and its myriad of ways to compose queries. You are cautious of premature optimization, but you know that in rare circumstances, like querying large datasets, instantiating vast amounts of ActiveRecord objects can be taxing for the interpreter (as demonstrated by the benchmarks that you have run against your code).

In those cases you know that you should take advantage of pluck to extract only the essential data, without instantiating any models.

In rare cases, when there is no ActiveRecord::Relation to send pluck to, or when raw SQL is the only viable solution 1, a different approach might be needed. That’s when the select_* family of methods 2, available on the ActiveRecord::Base.connection object, comes handy. Its members are:

Each is useful depending on how many columns are being queried and how many rows of results are expected, but honestly, their names are not that intuitive to remember. This is where this cheat sheet comes in.

Given the following table:

idnamehex
1redf00
2green0f0
3blue00f

The data can be queried in the following ways:

Returns Single Row Returns Multiple Rows
Single Column select_value("SELECT name FROM colors WHERE id = 1")"red" select_values("SELECT name FROM colors")["red", "green", "blue"]
Multiple Columns select_rows("SELECT name, hex FROM colors")[["red", "f00"], ["green", "0f0"], ["blue", "00f"]]
select_one("SELECT name, hex FROM colors WHERE id = 1"){"name"=>"red", "hex"=>"f00"} select_all("SELECT id, name, hex FROM colors")#<ActiveRecord::Result:0x00007fef5b266ba8 @columns=["id", "name", "hex"], @rows=[[1, "red", "f00"], [2, "green", "0f0"], [3, "blue", "00f"]], @hash_rows=nil, @column_types={}>

With select_all, instead of a raw data structure, an instance of ActiveRecord::Result is returned. It responds to #columns and #rows, but most importantly it is enumerable and as such it can be used as a simple array of hashes.

result = ActiveRecord::Base.connection.select_all(
  "SELECT id, name, hex FROM colors"
)
result.columns
=> ["id", "name", "hex"]
result.rows
=> [
  [1, "red", "f00"],
  [2, "green", "0f0"],
  [3, "blue", "00f"]
]
result.to_a
=> [
  {"id"=>1, "name"=>"red", "hex"=>"f00"},
  {"id"=>2, "name"=>"green", "hex"=>"0f0"},
  {"id"=>3, "name"=>"blue", "hex"=>"00f"}
]

Check out the code that generated the examples in the above tables.

  1. Or when working with Rails versions where pluck wasn’t as capable (before Rails 4), or did not yet exist at all (before Rails 3.2). 

  2. Dating all the way back to Rails 1.0.