boddhisattva boddhisattva - 5 months ago 14
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 > ?', 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.

Answer

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 }
Comments