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

Wojtek Kruszewski · Answer 1 · 2013-04-21T10:22:57.797

96

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

edited Apr 21 '13 at 10:22

answered Sep 21 '12 at 11:38

Wojtek Kruszewski

13,940
6
38
38

4

So much faster than using ruby enumerable group_by! – Will Nathan Jun 02 '13 at 13:43
Wojtek, what about when there was nothing created in a month? You don't get anything for that month. – Matt Smith Dec 03 '13 at 00:23
1

@MattSmith I believe so. Fortunately it's easy to provide default value when reading values from the output of this query: ```output.fetch("2012-08-01 00:00:00", 0)``` – Wojtek Kruszewski Dec 04 '13 at 12:50
In my case, the keys are Ruby Time objects, so I can access the result with `result[Time.current.utc.beginning_of_month]`. Not sure why the answer shows the result with String keys, or if that's changed over the years. – stephen.hanson Feb 29 '16 at 21:02
Is there a sort? lol the keys are out of order – Arian Faurtosh Apr 06 '21 at 22:27

score 32 · Accepted Answer · edited Mar 20 '18 at 15:37

32

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 }

edited Mar 20 '18 at 15:37

abraham

46,583
10
100
152

answered Jan 11 '10 at 22:40

teich

1,692
2
16
19

16

`group_by` is not an ActiveRecord method, but rather a Ruby method on `Enumerable`. – Laurens Feb 19 '13 at 17:49
Just using `u.created_at.month` is shorter – s.krueger Oct 18 '13 at 13:44
22

Please not that this will load all records from the database, instantiate ActiveRecord objects, parse dates into ActiveSupport's time zone aware objects – all just to calculate number of records. – Wojtek Kruszewski Dec 01 '13 at 12:44
2

This is a bad practice because when you use .all it get all records in database which will cost you too much – Ahmad Hussain Jun 11 '14 at 18:43
Better way is to use database group function – Ahmad Hussain Jun 11 '14 at 18:44
Unless you have "smallish" set of objects then this method will not treat you well; ram usage of our server reached 1.3GB because of our usage of `#group_by`, as said by an earlier comment, this will instantiate so many Ruby objects that it'll clog your server. – Abdulaziz May 07 '16 at 23:52
2

What a waste of memory! Use @WojtekKruszewski solution: `@user.comments.group("DATE_TRUNC('month', created_at)").count` – webaholik Jan 12 '18 at 18:57
Adding case when `group_by` is required & want to instantiate AR objects. When dealing with different currencies, you can't simply DB level group & sum amount columns. – nitsujri Oct 10 '20 at 15:05

score 27 · Answer 3 · edited Mar 09 '23 at 12:42

27

Here is the more refined version of this

@user.comments.group("year(created_at)").group("month(created_at)").count

Also

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

edited Mar 09 '23 at 12:42

Abel

3,989
32
31

answered May 28 '14 at 07:30

Ahmad Hussain

2,443
20
27

Mike Tunnicliffe · Answer 4 · 2013-08-05T12:47:37.760

14

My guess would be something like:

@user.comments.count(:group => "year(created_at),month(created_at)")

Dry-code, ymmv

edited Aug 05 '13 at 12:47

answered May 24 '09 at 04:17

Mike Tunnicliffe

10,674
3
31
46

I believe this will group by month across years as well - so if you've got two years of data, all stuff in January year 1 and year 2. What I'm looking for is a way of telling for a 24 month window for example, how many per month. – teich May 24 '09 at 16:47
You could add a condition to limit the year also, like :conditions => [:created_at, "> #{24.months.ago}"]. Again, untested, but should be possible with something like this. – Mike Tunnicliffe May 24 '09 at 23:56
4

This seems mysql specific – Tachyons Oct 24 '16 at 06:16

score 4 · Answer 5 · answered Sep 05 '14 at 12:37

4

Use group_by

@user.comments.group_by(&:week)

class User < ActiveRecord::Base
  def week
    some_attribute_like_date.strftime('%Y-%W')
  end
end

This will give you a grouped list in the format of YYYY-WW

answered Sep 05 '14 at 12:37

Austio

5,939
20
34

score 2 · Answer 6 · answered Feb 04 '15 at 21:02

2

Check out the group date gem

https://github.com/ankane/groupdate

it has recent commits, works with postgresql, integrates easily with chart kick for fast charting, and works with time zones!!

answered Feb 04 '15 at 21:02

Pavan Katepalli

2,372
4
29
52

GroupDate is only working with UTC, so if you are using another timezone in your database, consider alternatives. – msdundar Oct 16 '17 at 18:55

score 2 · Answer 7 · answered May 24 '09 at 16:20

2

Check out the has_activity plugin.

answered May 24 '09 at 16:20

Jim Gilliam

49
2

Thanks for the pointer. Looks like has_activity is mysql only. My production host is postgresql. – teich May 24 '09 at 16:46

Grouping by week/month/etc & ActiveRecord?

7 Answers7

Linked