Reducing Number Of Queries Required For Blog Archives Page

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

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

Frederick Cheung wrote: