1

I want to find signup count daily, for the date range say this month. so 

starts_at = DateTime.now.beginning_of_month
ends_at = DateTime.now.end_of_month

dates = ((starts_at.to_date)..(ends_at.to_date)).to_a
dates.each_with_index do |date,i|
    User.where("created_at  >= ? and created_at <= ?", date, date.tomorrow)
end

So nearly 30 queries running, how to avoid running 30 query and do it in single query?

I need something like 

group_by(:created_at)

But in group by if there is no data present for particular date it's showing nothing, but I need date and count as 0

I followed this:

How do I group by day instead of date?

def group_by_criteria
  created_at.to_date.to_s(:db)
end

User.all.group_by(&:group_by_criteria).map {|k,v| [k, v.length]}.sort

Output

[["2016-02-05", 5], ["2016-02-06", 12], ["2016-02-08", 6]]

There is no data for 2016-02-05 so it should be included with count 0

Community
  • 1
  • 1
Anbazhagan P
  • 125
  • 1
  • 10

2 Answers2

3

I can't test it at the moment, but it should be possible to filter your date range and group it with a little help of your dbms like this:

User.select('DATE(created_at)').where("created_at >= ? and created_at <= ?", DateTime.now.beginning_of_month, DateTime.now.end_of_month).group('DATE(created_at)').count
guitarman
  • 3,290
  • 1
  • 17
  • 27
  • when i use group if there is data for particular date then it is showing date and count , else nothing. I no count i need date and count as 0 – Anbazhagan P Feb 09 '16 at 12:46
  • Your'e right. That's because the database can't return a result, if there's no data. Therefore you could add it manually. But so you have one query only. – guitarman Feb 09 '16 at 13:14
0

Would this do?

starts_at = DateTime.now.beginning_of_month
ends_at = DateTime.now.end_of_month
User.where(created_at: starts_at..ends_at).group("date(created_at)").count
# => {Tue, 09 Feb 2016=>151, Mon, 08 Feb 2016=>130}

Note that you won't get any results for dates when there has been zero creations, so you might want to do something like this:

Hash[*(starts_at..ends_at).to_a.flat_map{|d| [d, 0]}].merge(
  User.where(created_at: starts_at..ends_at).group("date(created_at)").count
)

Not pretty, but what happens there is you first create a hash with all dates in the range having zero values and merging the results from database into that hash.

Kimmo Lehto
  • 5,910
  • 1
  • 23
  • 32