The problem with rails by year

Question

I have a Post model that includes date_published. I want to display links on an index page to view all posts for a given year (a bit like where blogs have months you can view)

The only way I can think of returning years is to repeat all the messages and return a unique array of years in which the messages fall. But it seems like a long way. Does anyone have a better solution? Maybe it would be easier to just create a new table, so Post belongs_to: year

Any suggestions?

Answer 1

I would use one of them:

• a find_by_sql to get reporting years using any functionality available during the year available in your database. Probably allow against the index, which I expect you to have;

or

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

I like the second a lot, although it does not take into account the possibility that the year may not have messages!

Answer 2

You do not need 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 create links from that.

Answer 3

Use SQL search for date field (it is updated in my example)

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