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
On 7 May 2008, at 07:28, John T. wrote:
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 C. wrote:
On 7 May 2008, at 07:28, John T. wrote:
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
Looks like just what I need. Thanks Fred!