Rails - Nov 2007 - How to retrive the number of articles in a given month?

Hi!

Sorry if this is more a SQL question than a Rails question. I have a
model called Article that have an attribute called date. I want to
retrive from the database the oldest article. And for each month since
then the number of articles. It is for creating an Archives section in a
blog I''m programming.

Do you think it will be better to create a Month model and insert in
each article a pointer to its month?

Thanks!
-- 
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
-~----------~----~----~----~------~----~------~--~---

> Sorry if this is more a SQL question than a Rails question. I have a
> model called Article that have an attribute called date. I want to
> retrive from the database the oldest article. And for each month since
> then the number of articles. It is for creating an Archives section in a
> blog I''m programming.
>
> Do you think it will be better to create a Month model and insert in
> each article a pointer to its month?
No.  Just use find_by_sql to run a COUNT(*) query grouped by the month. 
Or see if AR.count can group for you.

-philip

--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---

you can do it with a little bit of ruby code too:
articles_count__by_year_and_month =  Article.find(:all, :conditions =>
[your conditions...]).inject({}){|h,e|  h[e.date.year]||={};
h[e.date.year][e.date.month]||=0; h[e.date.year][e.date.month] +=1;
h }
and you get an hash = {2007=>{11=> 2, 12 => 5}}
and you can easely get count for november 2006
articles_count__by_year_and_month[2006][11]

On Nov 15, 10:32 pm, Philip Hallstrom
<ra...-SUcgGwS4C16SUMMaM/qcSw@public.gmane.org>
wrote:
> > Sorry if this is more a SQL question than a Rails question. I have a
> > model called Article that have an attribute called date. I want to
> > retrive from the database the oldest article. And for each month since
> > then the number of articles. It is for creating an Archives section in
a
> > blog I''m programming.
>
> > Do you think it will be better to create a Month model and insert in
> > each article a pointer to its month?
>
> No.  Just use find_by_sql to run a COUNT(*) query grouped by the month.
> Or see if AR.count can group for you.
>
> -philip
--~--~---------~--~----~------------~-------~--~----~
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 15 Nov 2007, at 21:44, Jean-Sébastien wrote:
>
> you can do it with a little bit of ruby code too:
> articles_count__by_year_and_month =  Article.find(:all, :conditions =>
> [your conditions...]).inject({}){|h,e|  h[e.date.year]||={};
> h[e.date.year][e.date.month]||=0; h[e.date.year][e.date.month] +=1;
> h }
> and you get an hash = {2007=>{11=> 2, 12 => 5}}
> and you can easely get count for november 2006
> articles_count__by_year_and_month[2006][11]
>
It will be a lot more efficient to let the database do the work (and  
not instantiate all those article objects). You''d probably get away  
with it on a small blog since that would probably entail hundreds of  
articles rather than tens of thousands.

with mysql, you can do something like this

Article.count :all, :group =>
"DATE_FORMAT(date,''%Y-%m'')"

Dropping down a level you could do

connection.select_all "date, count(*) from articles group by  
YEAR(date), MONTH(date)"

Fred
> On Nov 15, 10:32 pm, Philip Hallstrom
<ra...-SUcgGwS4C16SUMMaM/qcSw@public.gmane.org> wrote:
>>> Sorry if this is more a SQL question than a Rails question. I have
a
>>> model called Article that have an attribute called date. I want to
>>> retrive from the database the oldest article. And for each month  
>>> since
>>> then the number of articles. It is for creating an Archives  
>>> section in a
>>> blog I''m programming.
>>
>>> Do you think it will be better to create a Month model and insert
in
>>> each article a pointer to its month?
>>
>> No.  Just use find_by_sql to run a COUNT(*) query grouped by the  
>> month.
>> Or see if AR.count can group for you.
>>
>> -philip
> >

--~--~---------~--~----~------------~-------~--~----~
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@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

in fact i like better use ruby Hash and Array than
''find_by_sql'' or
complex ''find'', both methods have the same result.
and i''m not sure that a group_by take less ressource than ordering a
hash with ruby: it depends database implementation (table size,
indexes etc...)

On Nov 15, 11:12 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 15 Nov 2007, at 21:44, Jean-Sébastien wrote:
>
>
>
> > you can do it with a little bit of ruby code too:
> > articles_count__by_year_and_month =  Article.find(:all, :conditions
=>
> > [your conditions...]).inject({}){|h,e|  h[e.date.year]||={};
> > h[e.date.year][e.date.month]||=0; h[e.date.year][e.date.month] +=1;
> > h }
> > and you get an hash = {2007=>{11=> 2, 12 => 5}}
> > and you can easely get count for november 2006
> > articles_count__by_year_and_month[2006][11]
>
> It will be a lot more efficient to let the database do the work (and
> not instantiate all those article objects). You''d probably get
away
> with it on a small blog since that would probably entail hundreds of
> articles rather than tens of thousands.
>
> with mysql, you can do something like this
>
> Article.count :all, :group =>
"DATE_FORMAT(date,''%Y-%m'')"
>
> Dropping down a level you could do
>
> connection.select_all "date, count(*) from articles group by
> YEAR(date), MONTH(date)"
>
> Fred
>
> > On Nov 15, 10:32 pm, Philip Hallstrom
<ra...-SUcgGwS4C16SUMMaM/qcSw@public.gmane.org> wrote:
> >>> Sorry if this is more a SQL question than a Rails question. I
have a
> >>> model called Article that have an attribute called date. I
want to
> >>> retrive from the database the oldest article. And for each
month
> >>> since
> >>> then the number of articles. It is for creating an Archives
> >>> section in a
> >>> blog I''m programming.
>
> >>> Do you think it will be better to create a Month model and
insert in
> >>> each article a pointer to its month?
>
> >> No.  Just use find_by_sql to run a COUNT(*) query grouped by the
> >> month.
> >> Or see if AR.count can group for you.
>
> >> -philip
--~--~---------~--~----~------------~-------~--~----~
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@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

ok, i''ll use more native sql and thanks for benchmark.

On Nov 16, 2:53 am, Philip Hallstrom
<ra...-SUcgGwS4C16SUMMaM/qcSw@public.gmane.org>
wrote:
> > in fact i like better use ruby Hash and Array than
''find_by_sql'' or
> > complex ''find'', both methods have the same result.
> > and i''m not sure that a group_by take less ressource than
ordering a
> > hash with ruby: it depends database implementation (table size,
> > indexes etc...)
>
> Up to you, but group by is *much* faster.  The below is from a fairly
> quiet server... not having to instantiate all those objects saves a lot of
> time...
>
> >> News.count
> => 2321
> >> Benchmark.bm do |x|
>
> ?>
> ?>   x.report { 10.times do
> ?>     News.connection.execute("RESET QUERY CACHE")>>   
News.count :all, :group =>
"DATE_FORMAT(created_at,''%Y-%m'')"
> >>   end }
>
> ?>   x.report { 10.times do
> ?>     News.find(:all).inject({}){|h,e|  h[e.created_at.year]||={};
> ?>       h[e.created_at.year][e.created_at.month]||=0;
> ?>       h[e.created_at.year][e.created_at.month] +=1; h }>>   end
}
>
> ?> end
>        user     system      total        real
>    0.000000   0.000000   0.000000 (  0.188863)
>   18.090000   0.490000  18.580000 ( 18.813636)
> => true
>
>
>
> > On Nov 15, 11:12 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> > wrote:
> >> On 15 Nov 2007, at 21:44, Jean-Sébastien wrote:
>
> >>> you can do it with a little bit of ruby code too:
> >>> articles_count__by_year_and_month =  Article.find(:all,
:conditions =>
> >>> [your conditions...]).inject({}){|h,e|  h[e.date.year]||={};
> >>> h[e.date.year][e.date.month]||=0; h[e.date.year][e.date.month]
+=1;
> >>> h }
> >>> and you get an hash = {2007=>{11=> 2, 12 => 5}}
> >>> and you can easely get count for november 2006
> >>> articles_count__by_year_and_month[2006][11]
>
> >> It will be a lot more efficient to let the database do the work
(and
> >> not instantiate all those article objects). You''d
probably get away
> >> with it on a small blog since that would probably entail hundreds
of
> >> articles rather than tens of thousands.
>
> >> with mysql, you can do something like this
>
> >> Article.count :all, :group =>
"DATE_FORMAT(date,''%Y-%m'')"
>
> >> Dropping down a level you could do
>
> >> connection.select_all "date, count(*) from articles group by
> >> YEAR(date), MONTH(date)"
>
> >> Fred
>
> >>> On Nov 15, 10:32 pm, Philip Hallstrom
<ra...-SUcgGwS4C16SUMMaM/qcSw@public.gmane.org> wrote:
> >>>>> Sorry if this is more a SQL question than a Rails
question. I have a
> >>>>> model called Article that have an attribute called
date. I want to
> >>>>> retrive from the database the oldest article. And for
each month
> >>>>> since
> >>>>> then the number of articles. It is for creating an
Archives
> >>>>> section in a
> >>>>> blog I''m programming.
>
> >>>>> Do you think it will be better to create a Month model
and insert in
> >>>>> each article a pointer to its month?
>
> >>>> No.  Just use find_by_sql to run a COUNT(*) query grouped
by the
> >>>> month.
> >>>> Or see if AR.count can group for you.
>
> >>>> -philip
--~--~---------~--~----~------------~-------~--~----~
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@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

> in fact i like better use ruby Hash and Array than
''find_by_sql'' or
> complex ''find'', both methods have the same result.
> and i''m not sure that a group_by take less ressource than ordering
a
> hash with ruby: it depends database implementation (table size,
> indexes etc...)
Up to you, but group by is *much* faster.  The below is from a fairly 
quiet server... not having to instantiate all those objects saves a lot of 
time...
>> News.count
=> 2321
>> Benchmark.bm do |x|
?>
?>   x.report { 10.times do
?>     News.connection.execute("RESET QUERY
CACHE")
>>     News.count :all, :group =>
"DATE_FORMAT(created_at,''%Y-%m'')"
>>   end }
>>
?>   x.report { 10.times do
?>     News.find(:all).inject({}){|h,e|  h[e.created_at.year]||={};
?>       h[e.created_at.year][e.created_at.month]||=0;
?>       h[e.created_at.year][e.created_at.month] +=1; h
}
>>   end }
>>
?> end
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.188863)
  18.090000   0.490000  18.580000 ( 18.813636)
=> true
>
> On Nov 15, 11:12 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>> On 15 Nov 2007, at 21:44, Jean-Sbastien wrote:
>>
>>
>>
>>> you can do it with a little bit of ruby code too:
>>> articles_count__by_year_and_month =  Article.find(:all, :conditions
=>
>>> [your conditions...]).inject({}){|h,e|  h[e.date.year]||={};
>>> h[e.date.year][e.date.month]||=0; h[e.date.year][e.date.month] +=1;
>>> h }
>>> and you get an hash = {2007=>{11=> 2, 12 => 5}}
>>> and you can easely get count for november 2006
>>> articles_count__by_year_and_month[2006][11]
>>
>> It will be a lot more efficient to let the database do the work (and
>> not instantiate all those article objects). You''d probably get
away
>> with it on a small blog since that would probably entail hundreds of
>> articles rather than tens of thousands.
>>
>> with mysql, you can do something like this
>>
>> Article.count :all, :group =>
"DATE_FORMAT(date,''%Y-%m'')"
>>
>> Dropping down a level you could do
>>
>> connection.select_all "date, count(*) from articles group by
>> YEAR(date), MONTH(date)"
>>
>> Fred
>>
>>> On Nov 15, 10:32 pm, Philip Hallstrom
<ra...-SUcgGwS4C16SUMMaM/qcSw@public.gmane.org> wrote:
>>>>> Sorry if this is more a SQL question than a Rails question.
I have a
>>>>> model called Article that have an attribute called date. I
want to
>>>>> retrive from the database the oldest article. And for each
month
>>>>> since
>>>>> then the number of articles. It is for creating an Archives
>>>>> section in a
>>>>> blog I''m programming.
>>
>>>>> Do you think it will be better to create a Month model and
insert in
>>>>> each article a pointer to its month?
>>
>>>> No.  Just use find_by_sql to run a COUNT(*) query grouped by
the
>>>> month.
>>>> Or see if AR.count can group for you.
>>
>>>> -philip
> >
>
--~--~---------~--~----~------------~-------~--~----~
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@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---