Improve efficiency for a map command?

Question

I have 4 databases in my rails program, Products, Offerings, OfferingProducts, and Orders

Sample Offering: Medium Kit

Medium Kit Contains 2 Products (This is set up in the OfferingProduct db) 1 Small 2 Medium

customer Orders 2 medium kits

this program should account for 2 Small and 4 Medium from this order and many orders similar

I'm trying to set up an inventory system to track how many of each product are sold every week so that I can reorder products. To calculate this I have:

def get_trend
  product = Product.first
  customer_orders =[]
  product.offerings.each do |o|
    customer_orders = customer_orders + o.orders
  end
  o=customer_orders.group_by { |t| t.date.beginning_of_week }
  y = []
  op=self.offering_products;
  o.sort.each do |week, orders|
    y << orders.map { |o| o.quantity*op.find_by_offering_id(o.offering).quantity }.sum
  end
  return y
end

This seems to work, but it takes about 20 seconds to calculate. Is there any way to make this faster? I know most of the time is taken by the

y << orders.map { |o| o.quantity*op.find_by_offering_id(o.offering).quantity }.sum

line which calculates the number of products ordered in a given week. Any thoughts?

Answer 1

Here is what I ended up doing thanks to the info from Iain

def get_trend
    sql = ActiveRecord::Base.connection()
    if Rails.env == "production"
        d=sql.execute("SELECT SUM(orders.quantity * offering_products.quantity), EXTRACT(ISOYEAR FROM orders.date) AS year, EXTRACT(WEEK FROM orders.date) AS week " +
        " FROM orders " +
        " INNER JOIN offerings ON offerings.id = orders.offering_id " + 
        " INNER JOIN offering_products ON offering_products.offering_id = offerings.id " +
        " INNER JOIN products ON products.id = offering_products.product_id " +
        " WHERE (products.id = #{self.id}) GROUP BY year, week ORDER BY year, week ")
        y=d.map { |a| a["sum"].to_i }
    else
        d=sql.execute("SELECT SUM(orders.quantity * offering_products.quantity), strftime('%G-%V', orders.date) AS year " +
        " FROM orders " +
        " INNER JOIN offerings ON offerings.id = orders.offering_id " +
        " INNER JOIN offering_products ON offering_products.offering_id = offerings.id " + 
        " INNER JOIN products ON products.id = offering_products.product_id " +
        " WHERE (products.id = #{self.id}) GROUP BY year")
        y=d.map { |a| a[0] }
    end
    return y
end

Update: needed to differentiate the code between production and local, because heroku uses postgresql, and the date functions work differently:( which also means the result isnt exactly the same, because the functions treat the first week of the year differently.