From 623d81139e90471da71c8a56406471e6d5de9f32 Mon Sep 17 00:00:00 2001 From: Dessalines Date: Fri, 21 Jul 2023 05:44:47 -0400 Subject: [PATCH] Make sure comments are sorted by hot_rank, then score. (#3667) --- crates/db_views/src/comment_view.rs | 6 ++---- .../down.sql | 4 ++++ .../up.sql | 10 ++++++++++ 3 files changed, 16 insertions(+), 4 deletions(-) create mode 100644 migrations/2023-07-19-163511_comment_sort_hot_rank_then_score/down.sql create mode 100644 migrations/2023-07-19-163511_comment_sort_hot_rank_then_score/up.sql diff --git a/crates/db_views/src/comment_view.rs b/crates/db_views/src/comment_view.rs index e98d9f87..1b77168d 100644 --- a/crates/db_views/src/comment_view.rs +++ b/crates/db_views/src/comment_view.rs @@ -362,12 +362,10 @@ impl<'a> CommentQuery<'a> { query = match self.sort.unwrap_or(CommentSortType::Hot) { 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()), + .then_order_by(comment_aggregates::score.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/migrations/2023-07-19-163511_comment_sort_hot_rank_then_score/down.sql b/migrations/2023-07-19-163511_comment_sort_hot_rank_then_score/down.sql new file mode 100644 index 00000000..9f784c06 --- /dev/null +++ b/migrations/2023-07-19-163511_comment_sort_hot_rank_then_score/down.sql @@ -0,0 +1,4 @@ +drop index idx_comment_aggregates_hot, idx_comment_aggregates_score; + +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); diff --git a/migrations/2023-07-19-163511_comment_sort_hot_rank_then_score/up.sql b/migrations/2023-07-19-163511_comment_sort_hot_rank_then_score/up.sql new file mode 100644 index 00000000..02eff3ed --- /dev/null +++ b/migrations/2023-07-19-163511_comment_sort_hot_rank_then_score/up.sql @@ -0,0 +1,10 @@ +-- Alter the comment_aggregates hot sort to sort by score after hot_rank. +-- Reason being, is that hot_ranks go to zero after a few days, +-- and then comments should be sorted by score, not published. + +drop index idx_comment_aggregates_hot, idx_comment_aggregates_score; + +create index idx_comment_aggregates_hot on comment_aggregates (hot_rank desc, score desc); + +-- Remove published from this sort, its pointless +create index idx_comment_aggregates_score on comment_aggregates (score desc); -- 2.42.0