In Rails, how to compute a value based on a set of child records and store it in the parent record
I have invoices that are made up of invoice items. Each item has a profit and I want to sum them up and store the total profit at the account level.
I used to do 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 total_profit to always be correct, so it needs to be updated whenever an invoice item is added, edited or removed. Also total_profit should probably be protected from direct editing.
source to share
you can try post-create, post-save, and pre-destroy methods to add or subtract an amount from the parent's total profit. This way, your parent will only be updated if changes are made to the account items.
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
source to share
Joe is on the right track, but his answer doesn't address all of your problems. You also need to customize the attribute total_profit
in Invoice
. First, you need to add a field with the appropriate migration. Then you will want to protect this attribute with
attr_protected :total_profit
Or better yet:
attr_accessible ALL_NON_PROTECTED_ATTRIBUTES
It also does not hurt to establish a method of forced recalculation total_profit
. You end up with 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 might be a little overkill for your particular application, but hopefully it gives you some ideas on what might be best for you.
source to share
So I figured Peter suggested adding total_proft to Invoices with the appropriate migration.
Then, as Johannes suggested, I used ActiveRecord :: Callbacks in 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 you create an invoice and invoiceitem together. It starts normally, the INSERT SQL statement runs first for the invoice. You can then save the InvoiceItem record with the new invoice ID. However, after doing this, my above code triggers the request ...
SELECT sum(`invoice_items`.profit) AS sum_profit
FROM `invoice_items`
WHERE (`invoice_items`.invoice_id = NULL)
For some reason invoice_id is NULL even though it was just used to insert invoice_item.
source to share