Grouping by week/month/etc & ActiveRecord?

Question

I'm doing some statics calculation in my product. A user has performed a number of operations, let's say posted comments. I want to be able to show them how many comments they've posted per week for the past month, or per month for the past year.

Is there any way with activerecord to group this way? Is my best best to simply do this manually - to iterate over the records summing based on my own criteria?

class User < ActiveRecord::Base   has_many :comments end  class Comments < ActiveRecord::Base   belongs_to :user end  @user.comments(:all).map {|c| ...do my calculations here...} 

or is there some better way?

thanks! Oren

Answer 1

In Postgres you can do:

@user.comments.group("DATE_TRUNC('month', created_at)").count 

to get:

{"2012-08-01 00:00:00"=>152, "2012-07-01 00:00:00"=>57, "2012-09-01 00:00:00"=>132} 

It accepts values from "microseconds" to "millennium" for grouping: http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

Answer 2

In this case, the best solution for me was to either do it in straight SQL, or to use the Ruby group_by function:

@user.all.group_by{ |u| u.created_at.beginning_of_month }