# Better way to count Active Record Data?

**URL:** <https://www.ruby-forum.com/t/better-way-to-count-active-record-data/182433>\
**Category:** Rails\
**Created:** [January 28, 2010, 12:05am UTC](https://www.ruby-forum.com/t/better-way-to-count-active-record-data/182433 "2010-01-28T00:05:55Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![mrkohrea](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/4491bb/32.png) [@mrkohrea](https://www.ruby-forum.com/u/mrkohrea)\
**Post date:** [January 28, 2010, 12:05am UTC](https://www.ruby-forum.com/t/better-way-to-count-active-record-data/182433/1 "2010-01-28T00:05:55Z")

</div>

I am trying to get hourly counts of orders between different price  
ranges from my database. I’m thinking there has got to be a better way  
than looping through each record and checking if the created\_at time  
fits between hour 1, 2, 3, 4, etc of the day and then returning the  
count.

Currently I am displaying just a total of the days sales that are  
between different dollar amounts this way:

In my controller I grab all orders that are from today and return those  
to the view as @orders

In my view I display the number sold that are in a particular price  
range by the following helper method:

def number\_between\_75\_and\_100\_sold  
count = 0  
for n in @orders  
if n.total \>= 75 && n.total \< 100  
count = count + 1  
end  
end  
return count  
end

This helper method feels ugly to me. I have 3 others that give me  
different order total ranges. Better way?

If I use this same logic to count sales per hour I am going to end up  
with 24 more helpers that count those sales for each hour by looking at  
created\_at times between a range. This is going to be slow, ugly and  
lame.

I appreciate any advice.

---

<div class="post-metadata">

**Author:** ![mrkohrea](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/4491bb/32.png) [@mrkohrea](https://www.ruby-forum.com/u/mrkohrea)\
**Post date:** [January 28, 2010, 12:17am UTC](https://www.ruby-forum.com/t/better-way-to-count-active-record-data/182433/2 "2010-01-28T00:17:31Z")

</div>

Michael K. wrote:

> I am trying to get hourly counts of orders between different price  
> ranges from my database. I’m thinking there has got to be a better way  
> than looping through each record and checking if the created\_at time  
> fits between hour 1, 2, 3, 4, etc of the day and then returning the  
> count.

You are correct. Use the database to your advantage. Why would you be  
looping through records and checking created\_at in the first place?  
That’s what :conditions is for!

> Currently I am displaying just a total of the days sales that are  
> between different dollar amounts this way:
> 
> In my controller I grab all orders that are from today and return those  
> to the view as @orders
> 
> In my view I display the number sold that are in a particular price  
> range by the following helper method:
> 
> def number\_between\_75\_and\_100\_sold  
> count = 0  
> for n in @orders  
> if n.total \>= 75 && n.total \< 100  
> count = count + 1  
> end  
> end  
> return count  
> end
> 
> This helper method feels ugly to me. I have 3 others that give me  
> different order total ranges. Better way?
> 
> If I use this same logic to count sales per hour I am going to end up  
> with 24 more helpers that count those sales for each hour by looking at  
> created\_at times between a range. This is going to be slow, ugly and  
> lame.

It certainly is. Learn about SQL aggregate functions, and their  
abstraction layer (ActiveRecord::Calculations). What you want is  
something like

Order.count :conditions =\> [“created\_at \>= ? and total between ? and ?”,  
24.hours.ago, 75, 100]

That’s one SQL query that will only fetch the data you need.

> I appreciate any advice.

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![mrkohrea](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/4491bb/32.png) [@mrkohrea](https://www.ruby-forum.com/u/mrkohrea)\
**Post date:** [January 28, 2010, 12:34am UTC](https://www.ruby-forum.com/t/better-way-to-count-active-record-data/182433/3 "2010-01-28T00:34:09Z")

</div>

Marnen Laibow-Koser wrote:

> Michael K. wrote:
> 
> > I am trying to get hourly counts of orders between different price  
> > ranges from my database. I’m thinking there has got to be a better way  
> > than looping through each record and checking if the created\_at time  
> > fits between hour 1, 2, 3, 4, etc of the day and then returning the  
> > count.
> 
> You are correct. Use the database to your advantage. Why would you be  
> looping through records and checking created\_at in the first place?  
> That’s what :conditions is for!
> 
> > Currently I am displaying just a total of the days sales that are  
> > between different dollar amounts this way:
> > 
> > In my controller I grab all orders that are from today and return those  
> > to the view as @orders
> > 
> > In my view I display the number sold that are in a particular price  
> > range by the following helper method:
> > 
> > def number\_between\_75\_and\_100\_sold  
> > count = 0  
> > for n in @orders  
> > if n.total \>= 75 && n.total \< 100  
> > count = count + 1  
> > end  
> > end  
> > return count  
> > end
> > 
> > This helper method feels ugly to me. I have 3 others that give me  
> > different order total ranges. Better way?
> > 
> > If I use this same logic to count sales per hour I am going to end up  
> > with 24 more helpers that count those sales for each hour by looking at  
> > created\_at times between a range. This is going to be slow, ugly and  
> > lame.
> 
> It certainly is. Learn about SQL aggregate functions, and their  
> abstraction layer (ActiveRecord::Calculations). What you want is  
> something like
> 
> Order.count :conditions =\> [“created\_at \>= ? and total between ? and ?”,  
> 24.hours.ago, 75, 100]
> 
> That’s one SQL query that will only fetch the data you need.

Thanks for your reply. I use :conditions in my query to setup the  
@order. I was just thinking that it would be worse to do a bunch of  
lookups to the database for each dataset I am looking for when I could  
just fetch all the records I need once and then loop through @order to  
find what I need.

I have it in my head that 1 query to the database is better than 24 to  
get the data I need for sales each hour of the day.

Is that not correct?

> > I appreciate any advice.
> 
> ## Best,
> 
> Marnen Laibow-Koser  
> [http://www.marnen.org](http://www.marnen.org)  
> removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![mrkohrea](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/4491bb/32.png) [@mrkohrea](https://www.ruby-forum.com/u/mrkohrea)\
**Post date:** [January 29, 2010, 12:52pm UTC](https://www.ruby-forum.com/t/better-way-to-count-active-record-data/182433/4 "2010-01-29T12:52:11Z")

</div>

> I have it in my head that 1 query to the database is better than 24 to  
> get the data I need for sales each hour of the day.
> 
> Is that not correct?

Sure it is correct in most cases. Hence the GROUP BY in SQL and :group  
in RoR:

> > Order.count :conditions =\> [“created\_at \>= ? and total between ? and ?”, 24.hours.ago, 75, 100], :group =\> “hour(created\_at)”  
> > =\> #\<OrderedHash {“12”=\>16, “13”=\>3}\>

If you still want to do the grouping and counting in Ruby, then I can  
offer this monstrosity 😉

> > a = Order.find(:all, :conditions =\> [“created\_at \>= ? and total between ? and ?”, 24.hours.ago, 75, 100]).group\_by {|order| order.created\_at.hour}.inject({}) {|hash, (hour, orders\_group)| hash[hour] = orders\_group.count; hash}  
> > =\> {12=\>16, 13=\>3}

## Regards, Rimantas

[http://rimantas.com/](http://rimantas.com/)

---

<div class="post-metadata">

**Author:** ![mrkohrea](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/4491bb/32.png) [@mrkohrea](https://www.ruby-forum.com/u/mrkohrea)\
**Post date:** [January 28, 2010, 12:39am UTC](https://www.ruby-forum.com/t/better-way-to-count-active-record-data/182433/5 "2010-01-28T00:39:38Z")

</div>

Michael K. wrote:  
[…]

> Thanks for your reply. I use :conditions in my query to setup the  
> @order. I was just thinking that it would be worse to do a bunch of  
> lookups to the database for each dataset I am looking for when I could  
> just fetch all the records I need once and then loop through @order to  
> find what I need.
> 
> I have it in my head that 1 query to the database is better than 24 to  
> get the data I need for sales each hour of the day.
> 
> Is that not correct?

It is correct. So use count and :group. The database will do all the  
work for you.

> > > I appreciate any advice.
> > 
> > ## Best,
> > 
> > Marnen Laibow-Koser  
> > [http://www.marnen.org](http://www.marnen.org)  
> > removed\_email\_address@domain.invalid