In Rails, how to calculate a value based on a set of child records and store it in the parent record

Question

I have invoices that are made up invoice items. Each item has a profit and I want to sum these up and store the total profit at the invoice level.

Previously I was doing this calculation on-the-fly, but to improve performance I now need to store this value in the database.

class InvoiceItem < ActiveRecord::Base
  belongs_to :invoice
end

class Invoice< ActiveRecord::Base
  has_many :invoice_items
  def total_profit
    invoice_items.sum(:profit)
  end
end

I want the total_profit to always be correct, so it needs to be updated whenever an invoice item is added, edited or deleted. Also the total_profit probably should be protected from being directly edited.

Answer 1

Hi,

you may try the 'after create', 'after save' and 'before destroy' callback methods to add or subtract the amount from the parents total profit. In this way your parent object will be updated only if changes are made to the invoice items.

Best regards, Joe

edit:

to give you some untested pseudocode hints:

class InvoiceItem < ActiveRecord::Base
  belongs_to :invoice
  before_destroy { |item| item.invoice.subtract(item.amount) }
  after_create   { .. }
  after_save     { .. }
end

Answer 2

Joe's on the right track, but his answer doesn't address all your issues. You also need to set up the total_profit attribute in the Invoice. First you'll need to add the field with the appropriate migration. Then you'll want to protect that attribute with

attr_protected :total_profit

Or better yet:

attr_accessible ALL_NON_PROTECTED_ATTRIBUTES

It also doesn't hurt to set up a means of forcing a recalculation of the total_profit as well. In the end you'd have something like this:

class Invoice < ActiveRecord::Base
  has_many :invoice_items

  attr_protected :total_profit

  def total_profit(recalculate = false)
    recalculate_total_profit if recalculate
    read_attribute(:total_profit)
  end

  private

    def recalculate_total_profit
      new_total_profit = invoice_items.sum(:profit)
      if new_total_profit != read_attribute(:total_profit)
        update_attribute(:total_profit, new_total_profit)
      else
        true
      end
    end

end

Of course this may be a bit overkill for your specific application but hopefully it gives you some ideas of what may be best for you.

Answer 3

So my solution was as Peter suggested adding the total_proft to Invoices with the appropriate migration.

Then as Johannes suggested, I used ActiveRecord::Callbacks on my child model:

class InvoiceItem < ActiveRecord::Base
  belongs_to :invoice

  def after_save
    self.update_total_profit
  end
  def after_destroy
    self.update_total_profit
  end
  def update_total_profit
    self.invoice.total_profit = self.invoice.invoice_items.sum(:profit)
    self.sale.save
  end

end

class Invoice< ActiveRecord::Base
  has_many :invoice_items
  def total_profit
    invoice_items.sum(:profit)
  end
end

PLEASE NOTE: For some reason, the above code does not work when when creating an invoice and invoiceitem together. It starts ok, an INSERT SQL statement fires first for the Invoice. Then with the new Invoice ID, the InvoiceItem record can be saved. However after this my above code triggers the query ...

SELECT sum(`invoice_items`.profit) AS sum_profit 
FROM `invoice_items` 
WHERE (`invoice_items`.invoice_id = NULL)

For some reason the invoice_id is NULL, even though it has just been used to insert the invoice_item.