What's the most efficient way to keep track of an accumulated lifetime sales figure?

Question

So I have a Vendor model, and a Sale model. An entry is made in my Sale model whenever an order is placed via a vendor.

On my vendor model, I have 3 cache columns. sales_today, sales_this_week, and sales_lifetime.

For the first two, I calculated it something like this:

  def update_sales_today
    today = Date.today.beginning_of_day
    sales_today = Sale.where("created_at >= ?", today).find_all_by_vendor_id(self.id)
    self.sales_today = 0
    sales_today.each do |s|
      self.sales_today = self.sales_today + s.amount
    end 
    self.save    
  end

So that resets that value everytime it is accessed and re-calculates it based on the most current records.

The weekly one is similar but I use a range of dates instead of today.

But...I am not quite sure how to do Lifetime data.

I don't want to clear out my value and have to sum all the Sale.amount for all the sales records for my vendor, every single time I update this record. That's why I am even implementing a cache in the first place.

What's the best way to approach this, from a performance perspective?

rossta · Accepted Answer · 2013-04-22T16:52:27.627

1

I might use ActiveRecord's sum method in this case (docs). All in one:

today = Date.today
vendor_sales = Sale.where(:vendor_id => self.id)

self.sales_today =     vendor_sales.
                        where("created_at >= ?", today.beginning_of_day).
                        sum("amount")

self.sales_this_week = vendor_sales.
                        where("created_at >= ?", today.beginning_of_week).
                        sum("amount")

self.sales_lifetime = vendor_sales.sum("amount")

This would mean you wouldn't have to load lots of sales objects in memory to add the amounts.

edited Apr 22 '13 at 16:52

answered Apr 22 '13 at 11:21

rossta

11,394
1
43
47

Hrmm...when you said `vendor_sales = Sum.where...` did you mean `Sale.where...`? This `sum` method looks interesting for sure. Btw, I really like this approach. It makes it much more DRY. – marcamillion Apr 22 '13 at 12:20
This is great! Didn't know this existed. Is this as efficient as having a cache column? – AlexBrand Apr 22 '13 at 16:46
I suppose the answer is "it depends". There's likely still benefit to caching the result on a column (or somewhere else, like in Redis, etc.) In any case, I'd typically prefer using the sum query rather than loading ruby objects to add values. – rossta Apr 22 '13 at 16:51
Yes, I meant `Sale`. Edited above. – rossta Apr 22 '13 at 16:52

score 0 · Answer 2 · answered Apr 22 '13 at 11:21

You can use callbacks on the create and destroy events for your Sales model:

class SalesController < ApplicationController

    after_save :increment_vendor_lifetime_sales
    before_destroy :decrement_vendor_lifetime_sales

    def increment_vendor_lifetime_sales
        vendor.update_attribute :sales_lifetime, vendor.sales_lifetime + amount
    end

    def decrement_vendor_lifetime_sales
        vendor.update_attribute :sales_lifetime, vendor.sales_lifetime - amount
    end
end

What's the most efficient way to keep track of an accumulated lifetime sales figure?

2 Answers2