ActiveRecord’s Select & Pluck

Column Down

select and pluck are two different and useful ways to return the values from one or more columns in a database table as a collection. For example, imagine you have a questions table in your database. Each question might have three fields: id, question_text, and a foreign_key from an associated table. To return the values from only the question_text column, you would use select or pluck. I’d like to explore the differences between these two methods and end with an application of pluck inside a Rails application.


select works in two unique ways: takes a block and works just like Array#select. { |m| m.field == value }

This builds an array of Ruby objects from the database for the scope, converting them into an array and iterating through them using Array#select.

select vs where
Passing a block into select with a large data set (i.e. many rows) is slower than using other strategies, like where:
Model.where(field: value)
because with { |m| m.field == value }, Rails will first convert all of the table rows into Ruby objects, and then run the given block for each Ruby object, while Model.where(field: value) generates a SQL statement that offloads the heavy lifting to SQL, which is faster than Ruby for this type of filtering.

ii. select modifies the SELECT statement for the SQL query so that only certain fields are retrieved:
# => [#<Model id: nil, field: "value">]

For example, from a project of mine:
# => #<ActiveRecord::Relation [#<Question id: nil, question_text: "What's 2+2?">, ...]>

This generates the following SQL:

SELECT "questions"."question_text" FROM "questions"

Please note that in this second example, the select method does not return an array; it returns an ActiveRecord::Relation object. As a result, you can chain other ActiveRecord::QueryMethods to it. For example:
# => #<ActiveRecord::Relation [#<Question id: nil, question_text: "What's 2+2?">, #<Question id: nil, question_text: "What's 2+3?">, #<Question id: nil, question_text: "What's 3+3?">, #<Question id: nil, question_text: "What's 2+9?">, #<Question id: nil, question_text: "What's 10+2?">]>

This generates the following SQL:

SELECT  "questions"."question_text" FROM "questions" LIMIT ?  [["LIMIT", 5]]


From the Rails Guides:

pluck can be used to query single or multiple columns from the underlying table of a model. It accepts a list of column names as argument and returns an array of values of the specified columns with the corresponding data type.

The important point here is that pluck returns an array. It does not return an ActiveRecord::Relation object, like select. So, for example:

# => ["Text1", "Text2", ...]
# this returns an array with all of the values from the question_text column from the questions table.

Because pluck converts a database query result into a Ruby array without constructing an ActiveRecord object it’s more performant than select.

However, that pluck returns an array means you cannot chain standard ActiveRecord queries onto pluck. Doing so will return a NoMethodError:

# => NoMethodError: undefined method `limit' for #<Array:0x007fc5c1dfb3b0>

But pluck does work on ActiveRecord objects, so the following will return the expected result:

#=> ["Text1", "Text2", "Text3", "Text4", "Text5"]

pluck can take multiple arguments to construct a multidimensional array:

Question.pluck(:question_text, :course_id)
#=> [["Text1", 16], ["Text2", 23], ...]

When Would I Use Pluck ?

I’ve used pluck to scope ActiveRecord model objects to determine which model objects have associated records in another table.

For example, take the following class:

class Appointment < ActiveRecord::Base

has_many :charges

If we want to determine which appointments have charges and which ones do not, we can use pluck in a scope. Here’s how:

First, remember that for Model.where(field: value), value can either be a single object (like integer, string, etc.), or it can be an array of objects.

So, the following is valid and will return all appointments with ids that are included in the array.

Appointment.where(id: [1,2,3,4,5])
# => returns all appointments with ids that are included in the array

Well, we know that pluck returns an array, so we can construct an array that includes the foreign keys we want to scope in the associated model. For example, if we want to determine which appointments have associated charges, we can create an array from the appointment_id column of the charges table, like this:

# => returns an array with all of the appointment_ids from the charges table, like: [1,2,3,4,5]

To determine which appointments have associated charges, we can write:

Appointment.where(id: Charge.pluck(:appointment_id))
# this is the same as Appointment.where(id: [1,2,3,4,5])

And converting this into a scope on the Appointment model:

class Appointment < ActiveRecord::Base
has_many :charges
  scope :received_payment_info, -> {    
where(id: Charge.pluck(:appointment_id)) }

So now when we want to determine which appointments have associated charges, we can write:

#=> #<ActiveRecord::Relation [#<Appointment id: 1 >, ...]

which generates the following SQL queries:

SELECT "charges"."appointment_id" FROM "charges"
SELECT "appointments".* FROM "appointments" WHERE ("appointments"."id" IN (35, 44, 82) OR "appointments"."id" IS NULL)

pluck allowed us to:

  1. efficiently query an associated database table to…
  2. generate an array from the associated table with all of the foreign_keys of objects from the primary table
  3. use that array to determine which objects in the primary table have associated objects in the secondary table

I hope you found this helpful. Enjoy plucking and selecting.