How to retrive the number of articles in a given month?

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

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

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...)

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

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