Skip to content

Instantly share code, notes, and snippets.

@rmm5t

rmm5t/OUTPUT.md

Last active Feb 19, 2021
Embed
What would you like to do?
How to properly introduce a new counter_cache to an existing Rails project.
class AddCommentsCountToPosts < ActiveRecord::Migration
def change
change_table :posts do |t|
t.integer :comments_count, default: 0
end
reversible do |dir|
dir.up { fast }
end
end
# DO THIS!
def fast
execute <<-SQL.squish
UPDATE posts
SET comments_count = (SELECT count(1)
FROM comments
WHERE comments.post_id = posts.id);
SQL
end
# NOT THIS!
def slow
Post.reset_column_information
say_with_time "Seeding posts.comments_count -- Better grab a coffee." do
print " -> "
Post.select(:id).find_in_batches do |posts|
print "."
posts.each do |s|
Post.reset_counters s.id, :comments
end
end
puts
Post.count
end
end
end

Fast/efficient approach:

-- execute("UPDATE posts SET comments_count = (SELECT count(1) FROM comments WHERE comments.post_id = posts.id)")
   -> 1.3197s

Slow/naïve approach:

-- Seeding posts.comments_count -- Better grab a coffee.
   -> ...........................
   -> 144.7302s
   -> 26900 rows
@levberlin

This comment has been minimized.

Copy link

@levberlin levberlin commented Sep 8, 2016

This is great! Will have to read up on my raw SQL to fully understand, but certainly faster.

@williantenfen

This comment has been minimized.

Copy link

@williantenfen williantenfen commented Jul 3, 2019

nice!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment