From ced3aa5bd8bc78fb7a1a98bd839531255b773a9c Mon Sep 17 00:00:00 2001
From: Dessalines <dessalines@users.noreply.github.com>
Date: Thu, 20 Jul 2023 10:44:23 -0400
Subject: [PATCH] Fixing hot_ranks and scores to append a published sort.
 (#3618)

- #3428
---
 crates/db_views/src/comment_view.rs           |  8 +++--
 crates/db_views/src/post_view.rs              |  8 +++--
 .../down.sql                                  | 26 ++++++++++++++++
 .../up.sql                                    | 30 +++++++++++++++++++
 4 files changed, 68 insertions(+), 4 deletions(-)
 create mode 100644 migrations/2023-07-14-154840_add_optimized_indexes_published/down.sql
 create mode 100644 migrations/2023-07-14-154840_add_optimized_indexes_published/up.sql

diff --git a/crates/db_views/src/comment_view.rs b/crates/db_views/src/comment_view.rs
index 889adeeb..e98d9f87 100644
--- a/crates/db_views/src/comment_view.rs
+++ b/crates/db_views/src/comment_view.rs
@@ -360,10 +360,14 @@ impl<'a> CommentQuery<'a> {
     };
 
     query = match self.sort.unwrap_or(CommentSortType::Hot) {
-      CommentSortType::Hot => query.then_order_by(comment_aggregates::hot_rank.desc()),
+      CommentSortType::Hot => query
+        .then_order_by(comment_aggregates::hot_rank.desc())
+        .then_order_by(comment_aggregates::published.desc()),
+      CommentSortType::Top => query
+        .order_by(comment_aggregates::score.desc())
+        .then_order_by(comment_aggregates::published.desc()),
       CommentSortType::New => query.then_order_by(comment::published.desc()),
       CommentSortType::Old => query.then_order_by(comment::published.asc()),
-      CommentSortType::Top => query.order_by(comment_aggregates::score.desc()),
     };
 
     // Note: deleted and removed comments are done on the front side
diff --git a/crates/db_views/src/post_view.rs b/crates/db_views/src/post_view.rs
index 30693afb..1d85df3c 100644
--- a/crates/db_views/src/post_view.rs
+++ b/crates/db_views/src/post_view.rs
@@ -416,8 +416,12 @@ impl<'a> PostQuery<'a> {
     }
 
     query = match self.sort.unwrap_or(SortType::Hot) {
-      SortType::Active => query.then_order_by(post_aggregates::hot_rank_active.desc()),
-      SortType::Hot => query.then_order_by(post_aggregates::hot_rank.desc()),
+      SortType::Active => query
+        .then_order_by(post_aggregates::hot_rank_active.desc())
+        .then_order_by(post_aggregates::published.desc()),
+      SortType::Hot => query
+        .then_order_by(post_aggregates::hot_rank.desc())
+        .then_order_by(post_aggregates::published.desc()),
       SortType::New => query.then_order_by(post_aggregates::published.desc()),
       SortType::Old => query.then_order_by(post_aggregates::published.asc()),
       SortType::NewComments => query.then_order_by(post_aggregates::newest_comment_time.desc()),
diff --git a/migrations/2023-07-14-154840_add_optimized_indexes_published/down.sql b/migrations/2023-07-14-154840_add_optimized_indexes_published/down.sql
new file mode 100644
index 00000000..5661a314
--- /dev/null
+++ b/migrations/2023-07-14-154840_add_optimized_indexes_published/down.sql
@@ -0,0 +1,26 @@
+-- Drop the new indexes
+drop index idx_post_aggregates_featured_local_most_comments;
+drop index idx_post_aggregates_featured_local_hot;
+drop index idx_post_aggregates_featured_local_active;
+drop index idx_post_aggregates_featured_local_score;
+drop index idx_post_aggregates_featured_community_hot;
+drop index idx_post_aggregates_featured_community_active;
+drop index idx_post_aggregates_featured_community_score;
+drop index idx_post_aggregates_featured_community_most_comments;
+drop index idx_comment_aggregates_hot;
+drop index idx_comment_aggregates_score;
+
+-- Add the old ones back in
+-- featured_local
+create index idx_post_aggregates_featured_local_hot on post_aggregates (featured_local desc, hot_rank desc);
+create index idx_post_aggregates_featured_local_active on post_aggregates (featured_local desc, hot_rank_active desc);
+create index idx_post_aggregates_featured_local_score on post_aggregates (featured_local desc, score desc);
+
+-- featured_community
+create index idx_post_aggregates_featured_community_hot on post_aggregates (featured_community desc, hot_rank desc);
+create index idx_post_aggregates_featured_community_active on post_aggregates (featured_community desc, hot_rank_active desc);
+create index idx_post_aggregates_featured_community_score on post_aggregates (featured_community desc, score desc);
+
+create index idx_comment_aggregates_hot on comment_aggregates (hot_rank desc);
+create index idx_comment_aggregates_score on comment_aggregates (score desc);
+
diff --git a/migrations/2023-07-14-154840_add_optimized_indexes_published/up.sql b/migrations/2023-07-14-154840_add_optimized_indexes_published/up.sql
new file mode 100644
index 00000000..94e426fc
--- /dev/null
+++ b/migrations/2023-07-14-154840_add_optimized_indexes_published/up.sql
@@ -0,0 +1,30 @@
+-- Drop the old indexes
+drop index idx_post_aggregates_featured_local_hot;
+drop index idx_post_aggregates_featured_local_active;
+drop index idx_post_aggregates_featured_local_score;
+drop index idx_post_aggregates_featured_community_hot;
+drop index idx_post_aggregates_featured_community_active;
+drop index idx_post_aggregates_featured_community_score;
+drop index idx_comment_aggregates_hot;
+drop index idx_comment_aggregates_score;
+
+-- Add a published desc, to the end of the hot and active ranks
+
+-- Add missing most comments index
+create index idx_post_aggregates_featured_local_most_comments on post_aggregates (featured_local desc, comments desc, published desc);
+create index idx_post_aggregates_featured_community_most_comments on post_aggregates (featured_community desc, comments desc, published desc);
+
+-- featured_local
+create index idx_post_aggregates_featured_local_hot on post_aggregates (featured_local desc, hot_rank desc, published desc);
+create index idx_post_aggregates_featured_local_active on post_aggregates (featured_local desc, hot_rank_active desc, published desc);
+create index idx_post_aggregates_featured_local_score on post_aggregates (featured_local desc, score desc, published desc);
+
+-- featured_community
+create index idx_post_aggregates_featured_community_hot on post_aggregates (featured_community desc, hot_rank desc, published desc);
+create index idx_post_aggregates_featured_community_active on post_aggregates (featured_community desc, hot_rank_active desc, published desc);
+create index idx_post_aggregates_featured_community_score on post_aggregates (featured_community desc, score desc, published desc);
+
+-- Fixing some comment aggregates ones
+create index idx_comment_aggregates_hot on comment_aggregates (hot_rank desc, published desc);
+create index idx_comment_aggregates_score on comment_aggregates (score desc, published desc);
+
-- 
2.44.1