Reputation: 577
I have an Account
model with a column role
.
I want to select distinct roles by created_at
date (for example all distinct roles created on 01.01.2018 etc) and get only values of column role
.
Selection of distinct roles works fine as a query, but when it comes to getting the values, I'm getting unexpected results.
If I'm just using a map
function on all the query results, everything works good and SQL query looks fine.
Account.where(id: 1..10).select(:created_at, :role).distinct.map(&:role)
Account Load (1.0ms) SELECT DISTINCT "accounts"."created_at", "accounts"."role" FROM "accounts" WHERE ("accounts"."id" BETWEEN $1 AND $2) [["id", 1], ["id", 10]]
=> ["admin", "manager", "manager", "manager", "manager", "manager", "manager", "manager", "manager"]
But if I want to change .map(&:role)
to .pluck(:role)
, that are the same by definition, pluck
method removes first distinct
condition and leaves only distinct
by role as we can see in the beginning of the query.
Account.where(id: 1..10).select(:created_at, :role).distinct.pluck(:role)
(0.7ms) SELECT DISTINCT "accounts"."role" FROM "accounts" WHERE ("accounts"."id" BETWEEN $1 AND $2) [["id", 1], ["id", 10]]
=> ["admin", "manager"]
In pluck
documentation (apidock) it's written, that pluck will use distinct only if the code looks like .pluck('distinct role')
Why does it work like this in my case? Is it some undocumented feature?
Upvotes: 1
Views: 1780
Reputation: 1124
The short answer to your question "Why does it work like this in my case?" is because it is supposed to work like this. You stated:
In pluck documentation . . . it's written, that pluck will use distinct only if the code looks like
.pluck('distinct role')
This is not accurate. The doc you referenced shows an example like this as a way to pluck with DISTINCT
, but does not say that this is the only way to apply the DISTINCT
SQL modifier. Since you have added .distinct
onto your ActiveRecord
relation, the resulting query will be SELECT DISTINCT
. This prompts SQL to give you unique values, not the pluck
method; pluck
is only returning exactly what your DB gave to it.
For a way to achieve what you are after using pluck
for distinct combinations of created_at
and role
, you can use a group
instead:
Account.where(id: 1..10).group(:created_at, :role).pluck(:role)
# => SELECT "accounts"."role" FROM "accounts" WHERE ("accounts"."id" BETWEEN $1 AND $2) GROUP BY "accounts"."created_at", "accounts"."role" [["id", 1], ["id", 10]]
The .group(:created_at, :role)
call (which adds a GROUP BY
SQL clause) will give you unique combinations of rows based on created_at
and role
(the same role
may appear multiple times if it is associated with multiple created_at
values). Then .pluck(:role)
will take only the values for role
.
Upvotes: 2