Rails: select unique values from a column
Ruby on-RailsActiverecordRuby on-Rails Problem Overview
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
Ruby on-Rails Solutions
Solution 1 - Ruby on-Rails
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']
Solution 2 - Ruby on-Rails
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.
Solution 3 - Ruby on-Rails
This works too.
Model.pluck("DISTINCT rating")
Solution 4 - Ruby on-Rails
If you want to also select extra fields:
Model.select('DISTINCT ON (models.ratings) models.ratings, models.id').map { |m| [m.id, m.ratings] }
Solution 5 - Ruby on-Rails
Model.uniq.pluck(:rating)
# SELECT DISTINCT "models"."rating" FROM "models"
This has the advantages of not using sql strings and not instantiating models
Solution 6 - Ruby on-Rails
Model.select(:rating).uniq
This code works as 'DISTINCT' (not as Array#uniq) since rails 3.2
Solution 7 - Ruby on-Rails
Model.select(:rating).distinct
Solution 8 - Ruby on-Rails
Another way to collect uniq columns with sql:
Model.group(:rating).pluck(:rating)
Solution 9 - Ruby on-Rails
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".
Solution 10 - Ruby on-Rails
If anyone is looking for the same with Mongoid, that is
Model.distinct(:rating)
Solution 11 - Ruby on-Rails
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)
Solution 12 - Ruby on-Rails
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"
Solution 13 - Ruby on-Rails
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
.
Solution 14 - Ruby on-Rails
Model.pluck("DISTINCT column_name")