Rails group by year problem

Question

I have a Post model which includes a date_published. I want to display links on the index page to view all the posts of a certain year (a bit like where blogs have months you can view by)

The only way I can think of returning the years is by iterating over all of the posts and returning a unique array of the years that posts fall in. But this seems like a long way round. Does anyone have a better solution to this? Maybe it would be easier just creating new table so a Post belongs_to :year

Any suggestions?

Answer 1

I'd use one of these:

• a find_by_sql to get the distinct years, using whatever year-revealing functionality is available in your database. Would probably resolve against an index, which I'd expect you'd have;

or

• Post.minimum(:date_published).year..Post.maximum(:date_published).year

I like the second one a lot, even though it doesn't account for the possibility that a year may have no posts!

Answer 2

Use an SQL search of the date time field (in my example it is updated_at)

years = ActiveRecord::Base.connection.select_all("SELECT DISTINCT DATE_FORMAT(updated_at, '%Y') AS updated_at FROM posts ORDER BY updated_at DESC")

This will return all the year values in the form

[{"updated_at"=>"2009"}, {"updated_at"=>"2008"}, {"updated_at"=>"1979"}, {"updated_at"=>"1922"}]

 years.each { |y| puts y['updated_at'] }
 2009
 2008
 1979
 1924

Answer 3

You don't have to iterate over all of them in your Ruby code. You can use the :group option with count:

>> Post.count(:all, :group => "Year(date_published)")
=> [["2005", 5], ["2006", 231], ["2007", 810], ["2009", 3]]

And then build the links from that.