Published
select_* cheat sheetYou 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:
| id | name | hex |
|---|---|---|
| 1 | red | f00 |
| 2 | green | 0f0 |
| 3 | blue | 00f |
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.