Rails - counter_cache for total sum

Question

I have Transactions table which is the M:M table between Users and Products.

class Transaction
  belongs_to :user
  belongs_to :product, counter_cache: :transactions_count
end

Inside Transaction table, I have quantity column.

While inside Products, I have transactions_count which stores how many times this product has been purchased.

But that counter cache only count the number of rows. Is there a way to count the sum of quantity?

I know I can use something like after_save :update_count, but is there a Rails convention that when followed will automate this task?

Thanks

possible duplicate of [:counter\_cache => true for storing sum](http://stackoverflow.com/questions/11019253/counter-cache-true-for-storing-sum) — Intrepidd, Mar 26 '14 at 09:45

score 4 · Accepted Answer · edited May 23 '17 at 11:52

I've personally found counter_cache to be very unreliable (giving negative values etc), and tend to shy away until it's improved

Something you may be interested in:

Alias Column

We wanted to do something similar (pull from join model), and found a reliable way was to use an SQL Alias Column:

#app/models/user.rb
has_many :transactions
has_many :products, -> { select("#{User.table_name}.*, SUM(#{Transaction.table_name}.quantity) AS total") }, through: :transactions, dependent: :destroy

This may need some work, but will help#

ActiveRecord Association Extension

After discovering the .delegate method, I wanted to see if we could implement something similar for join models. 2 weeks later, we had it working:

#app/models/message.rb
Class Message < ActiveRecord::Base
   has_many :image_messages
   has_many :images, through: :image_messages, extend: ImageCaption
end

#app/models/concerns/image_caption.rb
module ImageCaption

    #Load
    def load
        captions.each do |caption|
            proxy_association.target << caption
        end
    end

    #Private
    private

    #Captions
    def captions
        return_array = []
        through_collection.each_with_index do |through,i|
            associate = through.send(reflection_name)
            associate.assign_attributes({caption: items[i]}) if items[i].present?
            return_array.concat Array.new(1).fill( associate )
        end
        return_array
    end

    #######################
    #      Variables      #
    #######################

    #Association
    def reflection_name
        proxy_association.source_reflection.name
    end

    #Foreign Key
    def through_source_key
        proxy_association.reflection.source_reflection.foreign_key
    end

    #Primary Key
    def through_primary_key
        proxy_association.reflection.through_reflection.active_record_primary_key
    end

    #Through Name
    def through_name
        proxy_association.reflection.through_reflection.name
    end

    #Through
    def through_collection
        proxy_association.owner.send through_name
    end

    #Captions
    def items
        through_collection.map(&:caption)
    end

    #Target
    def target_collection
        #load_target
        proxy_association.target
    end

end

This basically concatenates attributes from our join model (image_messages) to the parent object (image). You could use it to sum up your quantity in your proxy_association through collection, appending to each product the user has

Thanks for the answer. But from what I understand, it counts the quantity every time we query the database? Will that consume memory or negligible? — hrsetyono, Mar 26 '14 at 10:03
Yep it will consume memory & add extra time. It's only a recommendation to help give some ideas. The `counter_cache` is just a column in the db with a number in it; so I suppose you could use your idea for `after_update` to update the quantity column :) — Richard Peck, Mar 26 '14 at 10:05
Thanks for that, I guess I will go with using `after_update` or `after_save` — hrsetyono, Mar 26 '14 at 10:07
Np! We actually made another script which uses [ActiveRecord Association Extensions](http://guides.rubyonrails.org/association_basics.html#association-extensions) to manipulate the `proxy_association` object. Can post the code if you want -- this uses memory, not SQL — Richard Peck, Mar 26 '14 at 10:10
it's been years since you answered, anyone could give updates? are active_record callbacks and counter cache already good for this tasks now? — buncis, Jan 28 '22 at 10:58

Rails - counter_cache for total sum

1 Answers1