How to retrive the number of articles in a given month?
Florencio Cano
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/.
Philip Hallstrom
> 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
Jean-Sébastien
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]
Frederick Cheung
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
Jean-Sébastien
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...@gmail.com>
wrote:
Philip Hallstrom
> 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