How to set a counter cache value to a given one?

Question

I am using Ruby on Rails 3.2.2 and I would like to set a counter cache value to a "custom" one. That is, at this time (in my migration file) I am trying to use the following code:

def up
  add_column :articles, :comments_count, :integer, :default => 0

  Article.reset_column_information

  Article.find_each do |article|
    # Note: The following code doesn't work (when I migrate the database it   
    # raises the error "comments_count is marked as readonly").
    Article.update_column(:comments_count, article.custom_comments.count)
  end
end

In other words, I would like to set the :comments_count value (a counter cache database table column) to a custom value (in my case that value is article.custom_comments.count - note: the custom_comments is not an ActiveRecord Association but a method stated in the Article model class; it returns an integer value as well) that is not related to a has_many associations.

Maybe, I could / should use something like

Article.reset_column_information

Article.find_each do |article|
  Article.reset_counters(article.id, ...)
end

but it seems that the reset_counters method cannot work without has_many associations.

How can I set the :comments_count counter cache value to a given value that is related to a "custom association"?

Matthias Winkelmann · Answer 1 · 2018-09-16T12:54:04.797

The accept answer includes the iterating method, which is wrong for existing values of comment_count other than 0: update_counter sets the counter relative to it's current values. To set an absolute value, do:

Article.update_counters(article.id, comments_count: comments.count - article.comments_count)

If you have to fetch each row's correct count anyway, you can also more easily use Article.reset_counters(article.id, :comments)

To do it with far fewer queries, use this:

Author
  .joins(:books)
  .select("authors.id, authors.books_count, count(books.id) as count")
  .group("authors.id")
  .having("authors.books_count != count(books.id)")
  .pluck(:id, :books_count, "count(books.id)")
  .each_with_index do |(author_id, old_count, fixed_count), index| 
    puts "at index %7i: fixed author id %7i, new books_count %4i,  previous count %4i" % [index, author_id, fixed_count, old_count] if index % 1000 == 0
    Author.update_counters(author_id, books_count: fixed_count - old_count)
  end

MrTheWalrus · Accepted Answer · 2012-10-09T14:40:18.013

You describe comments_count as a counter cache, yet a counter cache is strictly defined as the number of associated records in a has_many relationship, which you say this isn't.

If the only way to get the value you want is via method on Article, then you're going to have to iterate over all your Article objects and update each one.

Article.find_each do |article|
  article.update_attribute(:comments_count, article.custom_comments.count)
end

This is pretty inefficient, since it's loading and saving every object. If the definition of custom_comments (which you don't actually explain) is something you can express in SQL, it would undoubtedly be faster to do this update in the database. Which might look something like this:

CREATE TEMP TABLE custom_comment_counts_temp AS
  SELECT articles.id as id, count(comments.id) as custom_comments 
    FROM articles 
    LEFT JOIN comments ON articles.id = comments.article_id
    WHERE <whatever condition indicates custom comments>
    GROUP BY articles.id;

CREATE INDEX ON custom_comments_counts_temp(id);

UPDATE articles SET comments_count = (SELECT custom_comments FROM custom_comment_counts_temp WHERE custom_comment_counts_temp.id = articles.id);

DROP TABLE custom_comment_counts_temp;

(this assumes postgresql - if you're using mySQL or some other database, it may look different. If you're not using a relational database at all, it may not be possible)

Additionally, since it's not a counter cache according to Rails' fairly narrow definition, you'll need to write some callbacks that keep these values updated - probably an after_save callback on comment, something like this:

comment.rb:

after_save :set_article_custom_comments


def set_article_custom_comments
  a = self.article
  a.update_attribute(:comments_count, a.custom_comments.count)
end

The trouble with counter caches is it uses the default scope. This means if you want to count something where the children have been soft-deleted, the count will be 0. Then when you restore the object, presumably you would restore the children too. Yet the counter cache will still be 0. — Mohamad, Dec 04 '15 at 14:36

How to set a counter cache value to a given one?

2 Answers2