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

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!

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs