CODEWITHSUNDEEP
postgresqlruby-on-rails-4rails-activerecord
Perpherior
Perpherior

Reputation: 201

Ruby on Rails: How to get monthly count by using PG

Now I'm facing a issue that is I want to write a statement to return monthly count,

For example, in period of 2014-01 to 2014-12. return an order array like 

["Jan, 5", "Feb, 0",...,"Dec, 55" ]

The possible solution that I only know is 

1. get a scope to return monthly record
2. calculate the period number, like here is 12 
3. repeat 12.times to get record size for each month
4. build array

The problem is I have to repeat queries for 12 times! That's so weird. I know group_by could be a better choice, but no idea how to achieve the performance which I really want to be. Could anyone help me?

Upvotes: 0

Views: 274

Answers (2)

SHS
SHS

Reputation: 7744

Format your date column using Postgres's to_char and then use it in ActiveRecord's group method.

start = Date.new(2014, 1, 1)
finish = Date.new(2014, 12, 31)

range = start..finish

return_hash = ModelClass.
              where(created_at: range).
              group("to_char(created_at, 'Mon YYYY')").
              count

That will return a hash like {"Nov 2014" => 500}

To 'fill in the gaps' you can create a month_names array and do:

month_names.each{ |month| return_hash[month] ||= 0 }

Consider creating a new hash altogether that has keys sorted according to your month_names variable.

Then to get your desired output:

return_hash.map{ |month, count| "#{month}, #{count}" }

Upvotes: 1

user1322092
user1322092

Reputation: 4270

I use the groupdate gem (https://github.com/ankane/groupdate)

Then add .group_by_month(:created_at).count to your query

Upvotes: 0

Related Questions