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

**URL:** <https://www.ruby-forum.com/t/how-to-retrive-the-number-of-articles-in-a-given-month/120327>\
**Category:** Rails\
**Created:** [November 15, 2007, 8:08pm UTC](https://www.ruby-forum.com/t/how-to-retrive-the-number-of-articles-in-a-given-month/120327 "2007-11-15T20:08:36Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![fcano](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/e19adc/32.png) [@fcano](https://www.ruby-forum.com/u/fcano)\
**Post date:** [November 15, 2007, 8:08pm UTC](https://www.ruby-forum.com/t/how-to-retrive-the-number-of-articles-in-a-given-month/120327/1 "2007-11-15T20:08:36Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![fcano](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/e19adc/32.png) [@fcano](https://www.ruby-forum.com/u/fcano)\
**Post date:** [November 15, 2007, 10:01pm UTC](https://www.ruby-forum.com/t/how-to-retrive-the-number-of-articles-in-a-given-month/120327/2 "2007-11-15T22:01:42Z")

</div>

> 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

---

<div class="post-metadata">

**Author:** ![fcano](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/e19adc/32.png) [@fcano](https://www.ruby-forum.com/u/fcano)\
**Post date:** [November 15, 2007, 10:45pm UTC](https://www.ruby-forum.com/t/how-to-retrive-the-number-of-articles-in-a-given-month/120327/3 "2007-11-15T22:45:02Z")

</div>

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]

---

<div class="post-metadata">

**Author:** ![fcano](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/e19adc/32.png) [@fcano](https://www.ruby-forum.com/u/fcano)\
**Post date:** [November 15, 2007, 11:13pm UTC](https://www.ruby-forum.com/t/how-to-retrive-the-number-of-articles-in-a-given-month/120327/4 "2007-11-15T23:13:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![fcano](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/e19adc/32.png) [@fcano](https://www.ruby-forum.com/u/fcano)\
**Post date:** [November 15, 2007, 11:34pm UTC](https://www.ruby-forum.com/t/how-to-retrive-the-number-of-articles-in-a-given-month/120327/5 "2007-11-15T23:34:48Z")

</div>

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 C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![fcano](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/e19adc/32.png) [@fcano](https://www.ruby-forum.com/u/fcano)\
**Post date:** [November 16, 2007, 12:36am UTC](https://www.ruby-forum.com/t/how-to-retrive-the-number-of-articles-in-a-given-month/120327/6 "2007-11-16T00:36:47Z")

</div>

> 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

---

<div class="post-metadata">

**Author:** ![fcano](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/e19adc/32.png) [@fcano](https://www.ruby-forum.com/u/fcano)\
**Post date:** [November 16, 2007, 1:23am UTC](https://www.ruby-forum.com/t/how-to-retrive-the-number-of-articles-in-a-given-month/120327/7 "2007-11-16T01:23:32Z")

</div>

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