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.
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
Michael Kahle 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 marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Marnen Laibow-Koser wrote:> Michael Kahle 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 > marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org-- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Michael Kahle 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 >> marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org-- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
> 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/ -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.