boddhisattva boddhisattva - 1 year ago 49
MySQL Question

Group records by both month and year in Rails

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 > ?','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


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

Answer Source


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

In Rails:

Message.all.group_by { |m| m.created_at.beginning_of_month }