CODEWITHSUNDEEP
mysqlruby-on-railscountdistinct
Edward
Edward

Reputation: 3499

rails COUNT SELECT DISTINCT

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

Upvotes: 26

Views: 28099

Answers (3)

Mike Campbell
Mike Campbell

Reputation: 7978

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.

Upvotes: 42

dayudodo
dayudodo

Reputation: 521

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"

Upvotes: 1

JacobEvelyn
JacobEvelyn

Reputation: 3971

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

Upvotes: 20

Related Questions