rails COUNT SELECT DISTINCT
Solution 1
You can use distinct.count(:attribute_name)
.
(In Rails 3 use: count(:user_id, distinct: true)
instead)
Thus:
UserVideoWatching.where("created_at >= ? AND user_id != ?", 1.month.ago, User.elephant.id)
.group("DATE(created_at)").reorder('created_at').distinct.count(:user_id)
Not able to test but I think that'll produce the SQL you're after.
Solution 2
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 3
Should use distinct, in rails 5.0.1, distinct equal uniq, but
[11] pry(main)> Needremember.distinct.count(:word)
(1.1ms) SELECT DISTINCT COUNT(DISTINCT "needremembers"."word") FROM "needremembers"
[12] pry(main)> Needremember.uniq.count(:word)
DEPRECATION WARNING: uniq is deprecated and will be removed from Rails 5.1 (use distinct instead) (called from __pry__ at (pry):12)
(0.6ms) SELECT DISTINCT COUNT(DISTINCT "needremembers"."word") FROM "needremembers"
Related videos on Youtube
Comments
-
Edward almost 4 years
I'm recording the number of times users watch a series of videos. Now I'm trying to make a graph of the number of users who watch any video each day.
UserVideoWatching.where("created_at >= ? AND user_id != ?",1.month.ago, User.elephant.id).group("DATE(created_at)").reorder('created_at').count
produces the sql
SELECT COUNT(*) AS count_all, DATE(created_at) AS date_created_at FROM `user_video_watchings` WHERE (created_at >= '2013-01-27 10:43:24' AND user_id != 7) GROUP BY DATE(created_at) ORDER BY created_at
which produces the correct results for all videos watched each day, but as I said I want to only show each user once.
The sql I want is
SELECT COUNT(DISTINCT user_id) AS count_all, DATE(created_at) AS date_created FROM `user_video_watchings` WHERE (created_at >= '2013-01-27 10:33:18' AND user_id != 7) GROUP BY DATE(created_at) ORDER BY created_at
so i thought
UserVideoWatching.where("created_at >= ? AND user_id != ?",1.month.ago, User.elephant.id).group("DATE(created_at)").reorder('created_at').select('COUNT(DISTINCT user_id) AS count_all, DATE(created_at) AS date_created')
would do what I wanted. But this gives
[#<UserVideoWatching >, #<UserVideoWatching >]
rather than a hash.
Any ideas?
I'm using rails 3.1 and mysql
-
s2t2 about 10 yearsdeprecated in rails 4 see stackoverflow.com/a/19362288/670433 for the solution which is to use ModelName.distinct.count(:attribute_name)
-
zero_cool over 9 yearsHow do you return all of the attributes of the model?