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 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 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.
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.
