23

I'm on Ruby 1.9.2, Rails 3.0.x and I use MySQL DB. I have a Messages Model, where one can post new messages every day.

I have an index action where I want to display these messages grouped by both month and year. This basically is used to filter messages.

My query looks like this:-

@active_msgs = Messages.where('expiry > ?', Time.now).order('created_at DESC')

I used the group_by function in Rails, after referring to this post

My Group By Query is:-

@msgs_by_month = @active_msgs.all.group_by {|u| u.created_at.month }

I was returned a Hash called @msgs_by_month. This helped me group the messages by month, but I need to taken into account the year, to form a unique key , as it would help me differentiate between for e.g., Sept 2011 and Sept 2012.

My current key only is of type Hash[Month] ( e.g. Hash[9] => for month of September, I can display all appropriate values ). How can i get a unique key of type month, year which I can easily loop though to display all records grouped by Month and Year of creation.

Thank you

EDIT:-

I'm guessing one way to do this, is to make use the created_at.to_date api, provided here . I only wonder, how I can strip out the day from created_at.to_date to get a unique month and year combination key which could work with the group_by function.

Community
  • 1
  • 1
boddhisattva
  • 6,908
  • 11
  • 48
  • 72
  • possible duplicate of [MySQL Query GROUP BY day / month / year](http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year) – noodl Sep 21 '12 at 07:47
  • Nah, not duplicate. the q you're referring to is only MYSQL-related where this q is a rails + mysql.. – Philip Dec 10 '13 at 19:06

5 Answers5

37

In SQL:

select * from messages group by year(created_at), month(created_at);

In Rails:

Message.all.group_by { |m| m.created_at.beginning_of_month }
noodl
  • 17,143
  • 3
  • 57
  • 55
  • Thanks dude.. :). It works when I enter the where clause also into the query.. Just for info of all.. – boddhisattva Sep 21 '12 at 10:33
  • 2
    DownVote because it selects all record for a month avoiding the year. So Data of Dec 2017 & Dec 2018 falls under same HASH – Huzaifa Saifuddin Dec 27 '18 at 10:01
  • 5
    This solution should never be used. as @HuzaifaSaifuddin mentioned, this is selecting all records -- so it loads the entire messages table into memory and then uses the group_by method on the returned Object. This will fail to scale. – John Hinnegan Jul 05 '20 at 22:13
  • @JohnHinnegan do you have any idea how to group_by without loading all the records in memory while getting a hash on result like group_by method does? – medBouzid Jul 22 '22 at 14:45
  • @medBouzid look at the other answers which, IMHO, are better. You want to use the `group` method which translates into a SQL group. Other answers have working solutions. For a Rails-specific solution, the answer from @Dorian below will produce approximately the same SQL as the answer suggested here. – John Hinnegan Jul 23 '22 at 18:07
  • Like others said, this will pull all messages into memory which scales very badly – Patrick Dougall Dec 06 '22 at 23:51
3

Use Group Date.

It supports time zones, so if you ever need to - the code will easily evolve.

https://github.com/ankane/groupdate

Pavan Katepalli
  • 2,372
  • 4
  • 29
  • 52
3
Message.select("date_trunc('month', created_at) as month").group("month")
Feuda
  • 2,335
  • 30
  • 28
1

To group by year and by month (e.g. not having december 2020 with december 2021):

Message.group("date_trunc('year', created_at), date_trunc('month', created_at)")

Tested on PostgreSQL

Dorian
  • 7,749
  • 4
  • 38
  • 57
-1
ModelName.all.group_by { |m| m.created_at.month }

This will work, however month will be returned as the index. Ex. Nov would refer to 11

Cody Elhard
  • 655
  • 1
  • 7
  • 17