From ced3aa5bd8bc78fb7a1a98bd839531255b773a9c Mon Sep 17 00:00:00 2001 From: Dessalines 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.42.0