# Reducing Number Of Queries Required For Blog Archives Page

**URL:** <https://www.ruby-forum.com/t/reducing-number-of-queries-required-for-blog-archives-page/136902>\
**Category:** Rails\
**Created:** [May 7, 2008, 8:28am UTC](https://www.ruby-forum.com/t/reducing-number-of-queries-required-for-blog-archives-page/136902 "2008-05-07T08:28:15Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![johnt519](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/cab0a1/32.png) [@johnt519](https://www.ruby-forum.com/u/johnt519)\
**Post date:** [May 7, 2008, 8:28am UTC](https://www.ruby-forum.com/t/reducing-number-of-queries-required-for-blog-archives-page/136902/1 "2008-05-07T08:28:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![johnt519](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/cab0a1/32.png) [@johnt519](https://www.ruby-forum.com/u/johnt519)\
**Post date:** [May 7, 2008, 10:52am UTC](https://www.ruby-forum.com/t/reducing-number-of-queries-required-for-blog-archives-page/136902/2 "2008-05-07T10:52:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![johnt519](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/cab0a1/32.png) [@johnt519](https://www.ruby-forum.com/u/johnt519)\
**Post date:** [May 7, 2008, 9:10pm UTC](https://www.ruby-forum.com/t/reducing-number-of-queries-required-for-blog-archives-page/136902/3 "2008-05-07T21:10:19Z")

</div>

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!