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.
