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?

score 2 · Accepted Answer · answered Apr 30 '12 at 21:30

2

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

The rails guide goes into detail about it.

answered Apr 30 '12 at 21:30

Mitch Dempsey

38,725
6
68
74

I still cant find that great of documentation on it. When I specify the :counter_sql in the has_many association, it will be the default increment counter that counter_cache uses? – Kyle C Apr 30 '12 at 22:34
And does this become the default counter on the whole table? – Kyle C Apr 30 '12 at 22:41
@KyleC counter cache counts how many relations are in the relationship. It is not used as the auto-increment – Mitch Dempsey Apr 30 '12 at 22:52
So implementing a :counter_sql query will set the value of the counter_cache? THen should I setup the association just as I would with using a counter_cache then add the :counter_sql call? (sorry for all the questions) – Kyle C Apr 30 '12 at 23:27
No, the `counter_sql` should be the query that is used to get the value of the counter. (So it should be something like: `SELECT SUM(deposits) FROM transactions` ). – Mitch Dempsey Apr 30 '12 at 23:45
That being said, you are really using counter_cache wrong for this. It is meant to calculate the number of items in the relationship (`User.count`), you should just make some other field that is cached like User.balance, (maybe one that is updated using an observer on `Transaction`) – Mitch Dempsey Apr 30 '12 at 23:47
That is what I was trying to do, I wanted to add functionality like a counter_cache. I planned on added a balance column then my question was how to add/update that balance column . – Kyle C Apr 30 '12 at 23:50
You should look at putting an observer on `Transaction`, that could update the "balance" attribute on the related user. Then you can have a process on the user, that does like `recalculate` that can rebuild the balance from scratch if there are any discrepancies – Mitch Dempsey Apr 30 '12 at 23:52

Sandip Ransing · Answer 2 · 2015-12-08T11:39:05.263

I would suggest two solutions for this

Simple

First add balance column to the User model and Add callback inside Transaction model to update the user's balance based on transaction type.

Complex

If you think you'll also need deposit_amount and purchase amount that too without wasting more time then counter_culture is what you need here.

counter_culture is somewhat similar to rails counter_cache but allow us to do various customizations.

This will involve following changes:

Add counter_culture to your Gemfile:

gem 'counter_culture', '~> 0.1.33'

then do bundle install

Add columns deposit_amount and purchase_amount to the users table.

class AddDepositAmountPurchaseAmountToUsers < ActiveRecord::Migration
  def self.change
    add_column :users, :deposit_amount, :decimal, :default => 0
    add_column :users, :purchase_amount, :decimal, :default => 0
  end
end

then do rake db:migrate

Make appropriate changes to the code

Inside Transaction model

counter_culture :user,
  column_name: Proc.new {|model|
    (model.txn_type == Transaction::DEPOSIT) ? 'deposit_amount' : 'purchase_amount' },
  delta_column: :amount

Inside User model

  def balance
    @balance ||= deposit_amount - purchase_amount
  end

If you need more information on Counter Culture then please read here

Rails counter_cache Balance

2 Answers2