Reducing Number Of Queries Required For Blog Archives Page

rubyonrails-talk
1

Hi,

I'm writing the canonical blogging application and have come to the archives page. I need to display a matrix of years and months in such a way that only the months that contains posts are hyperlinked. The page will look like this:

2008: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2007: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2006: etc.

My dilemma is that I can see this being expensive in terms of SQL, because as far as I can see for each month within the given year you have to do a count of the posts for that month. Judicious use of caching can minimize the number of queries required but I'd still like to hear some opinions on whether there's a more efficient way to do this.

Thanks in advance,

John

2

Hi,

I'm writing the canonical blogging application and have come to the archives page. I need to display a matrix of years and months in
such a way that only the months that contains posts are hyperlinked. The page will look like this:

2008: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2007: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2006: etc.

Select count(*), YEAR(created_at), MONTH(created_at) from posts group by YEAR(created_at), MONTH(created_at)

Should do the trick. I can't remember if Post.count will handle such a
group by or whether you'll need to drop down a level for this.

Fred

3

Frederick Cheung wrote: