FIX: Migrate topic_users.bookmarked to false when it is incorrectly true (#10211)

FIX: Migrate topic_users.bookmarked to false when it is incorrectly true (#10211)

Follow up to https://github.com/discourse/discourse/pull/10188/files

There are still TopicUser records where bookmarked is true even though there are no Bookmark or PostAction records with the type of bookmark for the associated topic and user. This migration corrects this issue by setting bookmarked to false for these cases.

diff --git a/db/migrate/20200710013237_fix_topic_users_bookmarked_column_that_should_be_false.rb b/db/migrate/20200710013237_fix_topic_users_bookmarked_column_that_should_be_false.rb
new file mode 100644
index 0000000..e84cdc5
--- /dev/null
+++ b/db/migrate/20200710013237_fix_topic_users_bookmarked_column_that_should_be_false.rb
@@ -0,0 +1,19 @@
+# frozen_string_literal: true
+
+class FixTopicUsersBookmarkedColumnThatShouldBeFalse < ActiveRecord::Migration[6.0]
+  def up
+    # post_action_type_id 1 is bookmark
+    sql = <<~SQL
+    UPDATE topic_users SET bookmarked = FALSE WHERE ID IN (
+      SELECT DISTINCT topic_users.id FROM topic_users
+      LEFT JOIN bookmarks ON bookmarks.topic_id = topic_users.topic_id AND bookmarks.user_id = topic_users.user_id
+      LEFT JOIN post_actions ON post_actions.user_id = topic_users.user_id AND post_actions.post_action_type_id = 1 AND post_actions.post_id IN (SELECT id FROM posts WHERE posts.topic_id = topic_users.topic_id) 
+      WHERE topic_users.bookmarked = true AND (bookmarks.id IS NULL AND post_actions.id IS NULL))
+    SQL
+    DB.exec(sql)
+  end
+
+  def down
+    raise ActiveRecord::IrreversibleMigration
+  end
+end

GitHub sha: 9a2955d4

This commit appears in #10211 which was merged by martin.