Finding the total stock amount of a product across all stores

Question

I have a Shop model and a Product model. I also have a StockItem model which joins the two - StockItem has shop_id, product_id and quantity attributes.

I need to find the total quantity of each product in stock across all the shops and the way I'm doing this at the moment feels a bit clunky:

quantity = 0
Product.first.stock_items.collect { |si| quantity += si.quantity }

Can anyone suggest a more succinct method please?

Answer 1

You may count the total quantity for all products using one database query.

class Product < ActiveRecord::Base
  has_many :shops, :through => :stock_items
  has_many :stock_items
  scope :with_total, :select => '[products].id, [products].name, sum([si].quantity) as total', 
    :joins => "left outer join stock_items [si] on [si].product_id = [products].id",
    :group => "[products].id, [products].name"  
end

Now you can get the total with this:

Product.with_total.first.total

Answer 2

Product.first.stock_items.count