CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecord
agustaf
agustaf

Reputation: 683

SUM operation on attributes of children of multiple parent records

I have this method in my Product model that does what I need:

def self.available
  available = []
  Product.all.each do |product|
    if product.quantity > product.sales.sum(:quantity)
      available << product
    end
  end
  return available
end

But, I am wondering if there is a more efficient way to do this, maybe with only one call to the database.

Upvotes: 3

Views: 120

Answers (2)

David Aldridge
David Aldridge

Reputation: 52336

Well you might try:

Product.where("products.quantity > Coalesce((select sum(s.quantity) from sales s where s.product_id = products.id), 0)")

Upvotes: 3

Oss
Oss

Reputation: 4322

This creates a number of queries equal to the number of products you have due to the sum query. Here is a way I though of that will reduce database queries.

map = Sale.joins(:product)
  .group("products.id", "products.quantity").sum(:quantity).to_a

Which will produce an array similar to 

[[[1,20],30], [[2,45], 20]]

this corresponds to [[[product_id, product_quantity], sold_quantity ]]

Now loop over this array and compare the values.

available = []
map.each do |item|
  if item[0][1] > item[1]
    available << item[0][0]
  end
end

Now that you have the available array populated, perform another query. 

available = Product.where(id: available)

Now you got your same output in two queries instead of Product.count (N) number of queries. This solution can be inefficient sometimes but I will be updating it regularly if I had any ideas.

Upvotes: 0

Related Questions