FIX: Backfill topic_id for EmailLog (#13469)

FIX: Backfill topic_id for EmailLog (#13469)

In the previous commit 5222247 we added a topic_id column to EmailLog. This simply backfills it in batches. The next PR will get rid of the topic method defined on EmailLog in favour of belongs_to.

diff --git a/db/post_migrate/20210621234939_backfill_email_log_topic_id.rb b/db/post_migrate/20210621234939_backfill_email_log_topic_id.rb
new file mode 100644
index 0000000..5e2770e
--- /dev/null
+++ b/db/post_migrate/20210621234939_backfill_email_log_topic_id.rb
@@ -0,0 +1,35 @@
+# frozen_string_literal: true
+
+class BackfillEmailLogTopicId < ActiveRecord::Migration[6.1]
+  disable_ddl_transaction!
+  BATCH_SIZE = 30_000
+
+  def up
+    offset = 0
+    email_log_count = DB.query_single("SELECT COUNT(*) FROM email_logs").first
+
+    loop do
+      DB.exec(<<~SQL, offset: offset, batch_size: BATCH_SIZE)
+          WITH cte AS (
+            SELECT post_id
+            FROM email_logs
+            ORDER BY id
+            LIMIT :batch_size
+            OFFSET :offset
+          )
+          UPDATE email_logs
+          SET topic_id = posts.topic_id
+          FROM cte
+          INNER JOIN posts ON posts.id = cte.post_id
+          WHERE email_logs.post_id = cte.post_id
+      SQL
+
+      offset += BATCH_SIZE
+      break if offset > (email_log_count + BATCH_SIZE * 2)
+    end
+  end
+
+  def down
+    raise ActiveRecord::IrreversibleMigration
+  end
+end

GitHub sha: 69518bee15814a2be8aa9cbc24b1073ddc556fb2

This commit appears in #13469 which was approved by tgxworld. It was merged by martin.