How can I count the number of records that have a unique value in a particular field in ROR?
Ruby on-RailsRubyActiverecordRuby on-Rails Problem Overview
I have a record set that includes a date field, and want to determine how many unique dates are represented in the record set.
Something like:
Record.find(:all).date.unique.count
but of course, that doesn't seem to work.
Ruby on-Rails Solutions
Solution 1 - Ruby on-Rails
This has changed slightly in rails 4 and above :distinct => true
is now deprecated. Use:
Record.distinct.count('date')
Or if you want the date and the number:
Record.group(:date).distinct.count(:date)
Solution 2 - Ruby on-Rails
What you're going for is the following SQL:
SELECT COUNT(DISTINCT date) FROM records
ActiveRecord has this built in:
Record.count('date', :distinct => true)
Solution 3 - Ruby on-Rails
Outside of SQL:
Record.find(:all).group_by(&:date).count
ActiveSupport's Enumerable#group_by is indispensable.
Solution 4 - Ruby on-Rails
the latest #count
on rails source code only accept 1 parameter.
see: http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-count
so I achieved the requirement by
Record.count('DISTINCT date')
Solution 5 - Ruby on-Rails
Detailing the answer:
Post.create(:user_id => 1, :created_on => '2010-09-29')
Post.create(:user_id => 1, :created_on => '2010-09-29')
Post.create(:user_id => 2, :created_on => '2010-09-29')
Post.create(:user_id => null, :created_on => '2010-09-29')
Post.group(:created_on).count
# => {'2010-09-29' => 4}
Post.group(:created_on).count(:user_id)
# => {'2010-09-29' => 3}
Post.group(:created_on).count(:user_id, :distinct => true) # Rails <= 3
Post.group(:created_on).distinct.count(:user_id) # Rails = 4
# => {'2010-09-29' => 2}
Solution 6 - Ruby on-Rails
As I mentioned here, in Rails 4, using (...).uniq.count(:user_id)
as mentioned in other answers (for this question and elsewhere on SO) will actually lead to an extra DISTINCT
being in the query:
SELECT DISTINCT COUNT(DISTINCT user_id) FROM ...
What we actually have to do is use a SQL string ourselves:
(...).count("DISTINCT user_id")
Which gives us:
SELECT COUNT(DISTINCT user_id) FROM ...
Solution 7 - Ruby on-Rails
Also, make sure you have an index on the field in your db, or else that query will quickly become sloooow.
(It's much better to do this in SQL, otherwise you pull the entire db table into memory just to answer the count.)