Rails: select unique values from a column

Question

I already have a working solution, but I would really like to know why this doesn't work:

ratings = Model.select(:rating).uniq
ratings.each { |r| puts r.rating }

It selects, but don't print unique values, it prints all values, including the duplicates. And it's in the documentation: http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields

Another example with uniq http://stackoverflow.com/questions/8369812/rails-how-can-i-get-unique-values-from-column — manish nautiyal, Feb 11 '13 at 06:08

score 546 · Accepted Answer · edited Nov 17 '20 at 09:26

546

Model.select(:rating)

The result of this is a collection of Model objects. Not plain ratings. And from uniq's point of view, they are completely different. You can use this:

Model.select(:rating).map(&:rating).uniq

or this (most efficient):

Model.uniq.pluck(:rating)

Rails 5+

Model.distinct.pluck(:rating)

Update

Apparently, as of rails 5.0.0.1, it works only on "top level" queries, like above. Doesn't work on collection proxies ("has_many" relations, for example).

Address.distinct.pluck(:city) # => ['Moscow']
user.addresses.distinct.pluck(:city) # => ['Moscow', 'Moscow', 'Moscow']

In this case, deduplicate after the query

user.addresses.pluck(:city).uniq # => ['Moscow']

edited Nov 17 '20 at 09:26

Simon Fels

21
5

answered Mar 11 '12 at 21:07

Sergio Tulentsev

226,338
43
373
367

I did a: group(:rating).collect { |r| r.rating } Since map == collect, where can I read about this sintax you used (&:rating)? I don't see this in Ruby's documentation. – alexandrecosta Mar 11 '12 at 21:22
@user1261084: see [Symbol#to_proc](http://ruby-doc.org/core-1.9.3/Symbol.html#method-i-to_proc) to understand .map(&:rating). [PragDave explains](http://pragdave.pragprog.com/pragdave/2005/11/symbolto_proc.html) – dbenhur Mar 11 '12 at 21:49
@Sergio, since Model.select(:rating) returns an array of Model objects, what's the use of specifying select on a column? I am finding it hard to grok how it is different from Model.all. – Salil Mar 15 '12 at 00:14
@Salil: if you're referring to my second line, then `select(:rating)` gets all Model records, but only fills rating field (to save bandwidth). Then we transform these Model objects to plain rating values, which we can `uniq`. – Sergio Tulentsev Mar 15 '12 at 00:16
72

It's worth noting that `Model.uniq.pluck(:rating)` is the most efficient way of doing this - this generates SQL which use `SELECT DISTINCT` rather than applying `.uniq` to an array – Mikey Jun 19 '13 at 15:38
@Mikey's comment is the right way. No idea why this answer is marked as correct. It's a exploding bomb if run in production with lots of data – Henley Sep 15 '15 at 20:38
@HenleyChiu: what do you mean? The answer suggests using `pluck` too. – Sergio Tulentsev Sep 16 '15 at 07:31
@SergioTulentsev I would just get rid of the 2 prior answers, and just show the pluck one. It's the only efficient way. – Henley Sep 16 '15 at 14:04
29

In Rails 5, `Model.uniq.pluck(:rating)` will be `Model.distinct.pluck(:rating)` – neurodynamic Feb 23 '16 at 22:11
3

If you want to select unique values from has_many relationship you can always do `Model.related_records.group(:some_column).pluck(:some_column)` – Krzysztof Karski Feb 15 '18 at 08:00
There is a wrong information in the answer; `Model.select(:rating)` does not return an array of `Model` objects, it actually returns an instance of `Model::ActiveRecord_Relation`. – Foo Bar Zoo Mar 13 '18 at 22:40
@FooBarZoo: now yes. But 6 years ago maybe it was returning an array. – Sergio Tulentsev Mar 14 '18 at 04:52
@SergioTulentsev `Model.distinct.pluck(:rating)` works for me on Rails 4.2.5 too. – EliadL Mar 24 '19 at 09:55
Not working on rails 7 – Nick Roz Jan 05 '23 at 10:14

score 102 · Answer 2 · edited Apr 04 '14 at 19:46

102

If you're going to use Model.select, then you might as well just use DISTINCT, as it will return only the unique values. This is better because it means it returns less rows and should be slightly faster than returning a number of rows and then telling Rails to pick the unique values.

Model.select('DISTINCT rating')

Of course, this is provided your database understands the DISTINCT keyword, and most should.

edited Apr 04 '14 at 19:46

user664833

18,397
19
91
140

answered Nov 05 '12 at 11:35

kakubei

5,321
4
44
66

6

`Model.select("DISTINCT rating").map(&:rating)` to get an array of just the ratings. – Kris Mar 12 '13 at 13:52
Great for those with legacy apps using Rails 2.3 – Mikey Jun 19 '13 at 15:38
3

Yes..this works fantastic - however, but it only returns the DISTINCT attribute. How can you return the entire Model object as long as its distinct? So that you will have access to all of the attributes in the model in instances where the attribute is unique. – zero_cool Oct 09 '14 at 17:56
@Jackson_Sandland If you want a Model object, that would need to be instantiated from a record in the table. But you're not selecting a record just a unique value (from what may be multiple records). – Benissimo Jan 09 '15 at 15:34

score 78 · Answer 3 · edited Nov 25 '16 at 12:05

78

This works too.

Model.pluck("DISTINCT rating")

edited Nov 25 '16 at 12:05

Sergio Tulentsev

226,338
43
373
367

answered Nov 12 '12 at 12:27

Nat

2,689
2
29
35

I believe pluck is Ruby 1.9.x and up. Anyone using a previous version won't have it. If you are in 1.9x and above, the ruby docs say this also works: Model.uniq.pluck(:rating) – kakubei Apr 06 '13 at 08:05
7

`pluck` is a pure Rails > 3.2 method which has no dependency on Ruby 1.9.x See http://apidock.com/rails/v3.2.1/ActiveRecord/Calculations/pluck – Daniel Rikowski Sep 11 '13 at 13:34
4

Non-attribute arguments will be disallowed in Rails 6.1, so for those on 6.1+ the following should do the trick: `Model.pluck(Arel.sql("DISTINCT rating"))` – Rocket Appliances Nov 17 '20 at 22:12

score 40 · Answer 4 · answered May 05 '15 at 09:47

40

If you want to also select extra fields:

Model.select('DISTINCT ON (models.ratings) models.ratings, models.id').map { |m| [m.id, m.ratings] }

answered May 05 '15 at 09:47

Marcin Nowicki

655
6
4

1

`select extra fields` <3 <3 – cappie013 Aug 25 '16 at 12:24

score 27 · Answer 5 · answered Mar 19 '14 at 18:34

27

Model.uniq.pluck(:rating)

# SELECT DISTINCT "models"."rating" FROM "models"

This has the advantages of not using sql strings and not instantiating models

answered Mar 19 '14 at 18:34

Cameron Martin

5,952
2
40
53

4

This throws an error with Rails 5.1 / AR 5.1 => undefined method `uniq' – Graham Slick Aug 11 '17 at 12:06

kuboon · Answer 6 · 2023-05-08T07:51:03.557

25

Model.select(:rating).uniq

This code works as 'DISTINCT' (not as Array#uniq) since rails 3.2

Above Rails 6 (?) it should be

Model.select(:rating).distinct

edited May 08 '23 at 07:51

answered Jul 11 '12 at 17:48

kuboon

9,557
3
42
32

2

In Rails 6 (At least 6.0.3) this doesn't generate a `DISTINCT` clause, so it's a `SELECT models.rating FROM models` and then Array#uniq – Rocket Appliances Nov 17 '20 at 22:17

score 14 · Answer 7 · edited Sep 16 '15 at 14:22

14

Model.select(:rating).distinct

edited Sep 16 '15 at 14:22

Sergio Tulentsev

226,338
43
373
367

answered Apr 24 '14 at 07:38

hassan_i

301
2
8

6

This is the only officially correct answer that's also super efficient. Although, adding `.pluck(:rating)` at the end will make it exactly what the OP asked for. – Sheharyar Aug 10 '18 at 22:55

score 6 · Answer 8 · answered Aug 09 '17 at 14:34

6

Another way to collect uniq columns with sql:

Model.group(:rating).pluck(:rating)

answered Aug 09 '17 at 14:34

Slava Zharkov

237
3
7

Upvoted for providing an original solution to the problem. This may even be more performant than a DISTINCT clause depending on the DB. – Rocket Appliances Nov 17 '20 at 22:22

score 4 · Answer 9 · edited Feb 12 '16 at 19:04

If I am going right to way then :

Current query

Model.select(:rating)

is returning array of object and you have written query

Model.select(:rating).uniq

uniq is applied on array of object and each object have unique id. uniq is performing its job correctly because each object in array is uniq.

There are many way to select distinct rating :

Model.select('distinct rating').map(&:rating)

or

Model.select('distinct rating').collect(&:rating)

or

Model.select(:rating).map(&:rating).uniq

or

Model.select(:name).collect(&:rating).uniq

One more thing, first and second query : find distinct data by SQL query.

These queries will considered "london" and "london " same means it will neglect to space, that's why it will select 'london' one time in your query result.

Third and forth query:

find data by SQL query and for distinct data applied ruby uniq mehtod. these queries will considered "london" and "london " different, that's why it will select 'london' and 'london ' both in your query result.

please prefer to attached image for more understanding and have a look on "Toured / Awaiting RFP".

enter image description here

`map` & `collect` are aliases for the same method, there is no need to provide examples for both. — Adam Lassek, Apr 05 '14 at 04:13

score 3 · Answer 10 · answered Jul 08 '14 at 00:12

3

If anyone is looking for the same with Mongoid, that is

Model.distinct(:rating)

answered Jul 08 '14 at 00:12

Vassilis

2,801
1
20
16

this one does not work now, it now returns multiples. – EUPHORAY Dec 20 '18 at 10:13
does not return distinct – dowi Sep 18 '19 at 11:14

score 3 · Answer 11 · answered Apr 25 '15 at 13:04

Some answers don't take into account the OP wants a array of values

Other answers don't work well if your Model has thousands of records

That said, I think a good answer is:

    Model.uniq.select(:ratings).map(&:ratings)
    => "SELECT DISTINCT ratings FROM `models` "

Because, first you generate a array of Model (with diminished size because of the select), then you extract the only attribute those selected models have (ratings)

score 2 · Answer 12 · edited Nov 17 '20 at 20:41

2

You can use the following Gem: active_record_distinct_on

Model.distinct_on(:rating)

Yields the following query:

SELECT DISTINCT ON ( "models"."rating" ) "models".* FROM "models"

edited Nov 17 '20 at 20:41

Simon Fels

21
5

answered Nov 15 '20 at 22:41

Alex Stanovsky

1,286
1
13
28

score 0 · Answer 13 · answered Apr 19 '22 at 21:51

In my scenario, I wanted a list of distinct names after ordering them by their creation date, applying offset and limit. Basically a combination of ORDER BY, DISTINCT ON

All you need to do is put DISTINCT ON inside the pluck method, like follow

Model.order("name, created_at DESC").offset(0).limit(10).pluck("DISTINCT ON (name) name")

This would return back an array of distinct names.

score -1 · Answer 14 · edited Nov 17 '20 at 19:21

-1

Model.pluck("DISTINCT column_name")

edited Nov 17 '20 at 19:21

Simon Fels

21
5

answered Sep 14 '20 at 15:52

Victor Shinkevich

9
1
3

Rails: select unique values from a column

14 Answers14

Rails 5+

Update

Linked

Related