Rails - May 2008 - 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
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 7 May 2008, at 07:28, John Topley wrote:
>
> 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
> 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
> -- 
> Posted via http://www.ruby-forum.com/.
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Frederick Cheung wrote:
> On 7 May 2008, at 07:28, John Topley 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!

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---