AKovtunov
AKovtunov

Reputation: 577

Rails: Why does pluck method return uniq values?

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

Answers (1)

dave_slash_null
dave_slash_null

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

Related Questions