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.

Answers


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 }

Use Group Date.

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

https://github.com/ankane/groupdate


Need Your Help

Rails3 jquery autocomplete distinct values

jquery autocomplete ruby-on-rails-3 jquery-autocomplete

Is it possible to select DISTINCT values using Rails3 JQuery autocomplete gem?

Sort an array which is first increasing then decreasing

arrays algorithm sorting

Given an array which is having elements which are in increasing order till a max value and then numbers in decreasing order.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.