rails COUNT SELECT DISTINCT

Question

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

Answer 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.