Recalculate cache counter 120k records [Rails / ActiveRecord]
Next situation:
I have a poi model that has many pictures (1: n). I want to recalculate the counter_cache column because the values are inconsistent.
I tried to iterate inside ruby over each record, but it takes too long and sometimes goes away with some "segmentation fault" errors.
So I wonder if it can do this with a raw SQL query?
+2
a source to share
2 answers
I found a good solution on krautcomputing .
It uses reflections to find all the project cache queries, SQL queries to find only inconsistent objects, and uses Rails reset_counters to clean things up.
Unfortunately, it only works with "regular" counter caches (no class name, no custom counter cache names), so I clarified it:
Rails.application.eager_load!
ActiveRecord::Base.descendants.each do |many_class|
many_class.reflections.each do |name, reflection|
if reflection.options[:counter_cache]
one_class = reflection.class_name.constantize
one_table, many_table = [one_class, many_class].map(&:table_name)
# more reflections, use :inverse_of, :counter_cache etc.
inverse_of = reflection.options[:inverse_of]
counter_cache = reflection.options[:counter_cache]
if counter_cache === true
counter_cache = "#{many_table}_count"
inverse_of ||= many_table.to_sym
else
inverse_of ||= counter_cache.to_s.sub(/_count$/,'').to_sym
end
ids = one_class
.joins(inverse_of)
.group("#{one_table}.id")
.having("MAX(#{one_table}.#{counter_cache}) != COUNT(#{many_table}.id)")
.pluck("#{one_table}.id")
ids.each do |id|
puts "reset #{id} on #{many_table}"
one_class.reset_counters id, inverse_of
end
end
end
end
+1
a source to share