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