PERF: Prefer joins over subquery for `User#private_posts_for_user`.

PERF: Prefer joins over subquery for User#private_posts_for_user.

The subquery here prevents the planner from optimizing the query. As such, we prefer joining against the requried tables instead.

diff --git a/app/models/post.rb b/app/models/post.rb
index e07a78f..afaea02 100644
--- a/app/models/post.rb
+++ b/app/models/post.rb
@@ -80,7 +80,10 @@ class Post < ActiveRecord::Base
   register_custom_field_type(MISSING_UPLOADS_IGNORED, :boolean)
 
   scope :private_posts_for_user, ->(user) {
-    where("posts.topic_id IN (#{Topic::PRIVATE_MESSAGES_SQL})", user_id: user.id)
+    joins("LEFT JOIN topic_allowed_users ON topic_allowed_users.topic_id = posts.topic_id AND topic_allowed_users.user_id = #{user.id.to_i}")
+      .joins("LEFT JOIN group_users ON group_users.user_id = #{user.id.to_i}")
+      .joins("LEFT JOIN topic_allowed_groups ON topic_allowed_groups.topic_id = posts.topic_id AND topic_allowed_groups.group_id = group_users.group_id")
+      .where("topic_allowed_users.topic_id IS NOT NULL OR topic_allowed_groups.topic_id IS NOT NULL")
   }
 
   scope :by_newest, -> { order('created_at DESC, id DESC') }

GitHub sha: 05b43e5a