Rails counter_cache Balance

Question

Currently I am calculating a users balance by

   def balance
      transactions.where('txn_type = ?', Transaction::DEPOSIT).sum(:amount) -    
      transactions.where('txn_type = ?', Transaction::PURCHASE).sum(:amount)
   end

I am running a query that subtracts adds all the users deposits and subtracts all their purchases. This will not scale well when there are thousands of transactions. What would be the best approach to calculating a users balance? Is there a way to customize counter_cache to calculate this per user?

Answer 1

Yes, you can specify :counter_sql which will be used to set the counter_cache.

The rails guide goes into detail about it.