CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3
Justin
Justin

Reputation: 1956

Rails 3 Counting Records by Date

I am trying to build an array that looks like this via a model method:

[['3/25/13', 2], ['3/26/13', 1], ['3/27/13', 2]]

Where, the dates are strings and the numbers after them are the count of an table/object.

I have the following model method right now:

def self.weekly_count_array
  counts = count(group: "date(#{table_name}.created_at)", conditions: { created_at: 1.month.ago.to_date..Date.today }, order: "date(#{table_name}.created_at) DESC")
  (1.week.ago.to_date).upto(Date.today) do |x|
    counts[x.to_s] ||= 0
  end
  counts.sort
end

However, it doesn't return the count accurately (all values are zero). There seem to be some similar questions on SO that I've checked out, but can't seem to get them to work either.

Can someone help (1) let me know if this is the best way to do it, and (2) provide some guidance in terms of what the problem might be with the above code, if so? Thanks!

Upvotes: 1

Views: 2025

Answers (5)

Moh.Header
Moh.Header

Reputation: 58

Built on @kiddorails Answer,

so not to make a lot of Requests to the DataBase, Created a Hash from the ActiveRecord

& changed the group from .group('created_at') to .group('DATE(created_at)') to base it on date

def self.weekly_count_array
   # records = select('DATE(created_at) created_at, count(id) as id').group('created_at')
   records_hash = Hash[Download.select('DATE(created_at) created_at, count(id) as id').group('DATE(created_at)').map{|d|[d.created_at, d.id] }]
   1.month.ago.to_date.upto(Date.today).map do |d| 
     [ d, records_hash[d.to_date] || 0 ]
   end 
end

Upvotes: 0

kiddorails
kiddorails

Reputation: 13014

Fantastic answer by @Aditya Sanghi.

If you have the exact requirement, you can opt:

def self.weekly_count_array
  records = select('DATE(created_at) created_at, count(id) as id').group('created_at')
  1.week.ago.to_date.upto(Date.today).map do |d| 
    [d, records.where('DATE(created_at) = ?', d.to_date).first.try(:id) || 0]
  end 
end

Upvotes: 1

Aditya Sanghi
Aditya Sanghi

Reputation: 13433

Use this as a template if you wish

def self.period_count_array(from = (Date.today-1.month).beginning_of_day,to = Date.today.end_of_day)
  where(created_at: from..to).group('date(created_at)').count
end

This will return you a hash with dates as key and the count as value. (Rails 3.2.x)

Upvotes: 8

house9
house9

Reputation: 20614

maybe this is what you are trying to do?

class YourActiveRecordModel < ActiveRecord::Base

  def.self weekly_count_array
    records = self.select("COUNT(id) AS record_count, DATE(created_at) AS created")
      .group("DATE(created_at)")
      .where("created_at >= ?", 1.month.ago.to_date) 
      .where("created_at <= ?", Date.current)

    records.each do |x|
      puts x.record_count
      puts x.created # 2013-03-14

      # use I18n.localize(x.created, format: :your_format) 
      # where :your_format is defined in config/locales/en.yml (or other .yml)
    end        
  end
end

Upvotes: 1

Carlos Pereira
Carlos Pereira

Reputation: 242

You do not need a process to perform the count. Simply perform a query for this.

def self.weekly_count_array
    select("created_at, COUNT(created_at) AS count")
    where(created_at: 1.month.ago.to_date..Date.today)
    group("created_at")
    order("created_at DESC")
end

Upvotes: 0

Related Questions