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.
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
[email protected]
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
[email protected]
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/
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
[email protected]