How to count entries for every day in a date range

Question

I have a voting system. Each vote has a rating.

I want to be able to count how many votes were made per day?

The code below adds estimates of each vote for each day and returns a hash with the total and date in the form of a key:

ratings = where(created_at: start.beginning_of_day..Time.zone.now).group("created_at").select("created_at, sum(score) as total_score")
ratings.each_with_object({}) do |rating, scores|
 scores[rating.created_at.to_date] = rating.total_score
end

I want to be able to count how many votes were made each day. Any ideas?

Answer 1

You can do what you want using the nicer syntax:

from = start.beginning_of_day
to   = Time.zone.now
Rating.group('date(created_at)').where(created_at: from .. to).count(:score)

This will return a hash with a date (created_at) as the key and a counter (: score) as the value for each day.

:   { "2014-01-21" = > 100, "2014-01-22" = > 1213}

SQL:

SELECT COUNT("ratings"."score") AS count_score, date(created_at) AS date_created_at FROM "ratings" WHERE ("ratings"."created_at" BETWEEN '2014-01-21' AND '2014-01-24') GROUP BY date(created_at)

Answer 2

MySQL

SELECT date(created_at) as date, count(*) as count FROM `ratings` WHERE (created_at between '2014-01-21 00:00:00' AND '2014-01-24 08:16:46') GROUP BY date(created_at)

Rails:

start_date = Date.parse("2014-01-21").beginning_of_day
end_time = Time.zone.now
Rating.group('date(created_at)').select('date(created_at) as date, count(*) as count').where(["created_at between ? AND ?", start_time, end_time])