From f5209fffc1de527db7ea007d463c158b36fda515 Mon Sep 17 00:00:00 2001
From: c-andy-candies <74613851+c-andy-candies@users.noreply.github.com>
Date: Mon, 26 Jun 2023 21:03:35 +0200
Subject: [PATCH] Feature add three six and nine months options backend (#3226)

Co-authored-by: Dessalines <dessalines@users.noreply.github.com>
---
 crates/db_schema/src/lib.rs                        |  3 +++
 crates/db_schema/src/utils.rs                      |  5 ++++-
 crates/db_views/src/post_view.rs                   | 12 ++++++++++++
 crates/db_views_actor/src/person_view.rs           |  9 +++++++++
 .../down.sql                                       | 14 ++++++++++++++
 .../up.sql                                         |  4 ++++
 6 files changed, 46 insertions(+), 1 deletion(-)
 create mode 100644 migrations/2023-06-20-191145_add_listingtype_sorttype_3_6_9_months_enums/down.sql
 create mode 100644 migrations/2023-06-20-191145_add_listingtype_sorttype_3_6_9_months_enums/up.sql

diff --git a/crates/db_schema/src/lib.rs b/crates/db_schema/src/lib.rs
index 04ec4e7d..acb069ca 100644
--- a/crates/db_schema/src/lib.rs
+++ b/crates/db_schema/src/lib.rs
@@ -63,6 +63,9 @@ pub enum SortType {
   TopHour,
   TopSixHour,
   TopTwelveHour,
+  TopThreeMonths,
+  TopSixMonths,
+  TopNineMonths,
 }
 
 #[derive(EnumString, Display, Debug, Serialize, Deserialize, Clone, Copy)]
diff --git a/crates/db_schema/src/utils.rs b/crates/db_schema/src/utils.rs
index fdd44563..44230d10 100644
--- a/crates/db_schema/src/utils.rs
+++ b/crates/db_schema/src/utils.rs
@@ -259,7 +259,10 @@ pub fn post_to_comment_sort_type(sort: SortType) -> CommentSortType {
     | SortType::TopAll
     | SortType::TopWeek
     | SortType::TopYear
-    | SortType::TopMonth => CommentSortType::Top,
+    | SortType::TopMonth
+    | SortType::TopThreeMonths
+    | SortType::TopSixMonths
+    | SortType::TopNineMonths => CommentSortType::Top,
   }
 }
 
diff --git a/crates/db_views/src/post_view.rs b/crates/db_views/src/post_view.rs
index 14cf7fe1..d1e974d8 100644
--- a/crates/db_views/src/post_view.rs
+++ b/crates/db_views/src/post_view.rs
@@ -430,6 +430,18 @@ impl<'a> PostQuery<'a> {
         .filter(post_aggregates::published.gt(now - 12.hours()))
         .then_order_by(post_aggregates::score.desc())
         .then_order_by(post_aggregates::published.desc()),
+      SortType::TopThreeMonths => query
+        .filter(post_aggregates::published.gt(now - 3.months()))
+        .then_order_by(post_aggregates::score.desc())
+        .then_order_by(post_aggregates::published.desc()),
+      SortType::TopSixMonths => query
+        .filter(post_aggregates::published.gt(now - 6.months()))
+        .then_order_by(post_aggregates::score.desc())
+        .then_order_by(post_aggregates::published.desc()),
+      SortType::TopNineMonths => query
+        .filter(post_aggregates::published.gt(now - 9.months()))
+        .then_order_by(post_aggregates::score.desc())
+        .then_order_by(post_aggregates::published.desc()),
     };
 
     let (limit, offset) = limit_and_offset(self.page, self.limit)?;
diff --git a/crates/db_views_actor/src/person_view.rs b/crates/db_views_actor/src/person_view.rs
index c7876cc1..2a7a2ce7 100644
--- a/crates/db_views_actor/src/person_view.rs
+++ b/crates/db_views_actor/src/person_view.rs
@@ -122,6 +122,15 @@ impl<'a> PersonQuery<'a> {
       SortType::TopTwelveHour => query
         .filter(person::published.gt(now - 12.hours()))
         .order_by(person_aggregates::comment_score.desc()),
+      SortType::TopThreeMonths => query
+        .filter(person::published.gt(now - 3.months()))
+        .order_by(person_aggregates::comment_score.desc()),
+      SortType::TopSixMonths => query
+        .filter(person::published.gt(now - 6.months()))
+        .order_by(person_aggregates::comment_score.desc()),
+      SortType::TopNineMonths => query
+        .filter(person::published.gt(now - 9.months()))
+        .order_by(person_aggregates::comment_score.desc()),
     };
 
     let (limit, offset) = limit_and_offset(self.page, self.limit)?;
diff --git a/migrations/2023-06-20-191145_add_listingtype_sorttype_3_6_9_months_enums/down.sql b/migrations/2023-06-20-191145_add_listingtype_sorttype_3_6_9_months_enums/down.sql
new file mode 100644
index 00000000..f8493fd2
--- /dev/null
+++ b/migrations/2023-06-20-191145_add_listingtype_sorttype_3_6_9_months_enums/down.sql
@@ -0,0 +1,14 @@
+-- update the default sort type
+update local_user set default_sort_type = 'TopDay' where default_sort_type in ('TopThreeMonths', 'TopSixMonths', 'TopNineMonths');
+
+-- rename the old enum
+alter type sort_type_enum rename to sort_type_enum__;
+-- create the new enum
+CREATE TYPE sort_type_enum AS ENUM ('Active', 'Hot', 'New', 'Old', 'TopDay', 'TopWeek', 'TopMonth', 'TopYear', 'TopAll', 'MostComments', 'NewComments');
+
+-- alter all you enum columns
+alter table local_user
+  alter column default_sort_type type sort_type_enum using default_sort_type::text::sort_type_enum;
+
+-- drop the old enum
+drop type sort_type_enum__;
diff --git a/migrations/2023-06-20-191145_add_listingtype_sorttype_3_6_9_months_enums/up.sql b/migrations/2023-06-20-191145_add_listingtype_sorttype_3_6_9_months_enums/up.sql
new file mode 100644
index 00000000..85bcfad7
--- /dev/null
+++ b/migrations/2023-06-20-191145_add_listingtype_sorttype_3_6_9_months_enums/up.sql
@@ -0,0 +1,4 @@
+-- Update the enums
+ALTER TYPE sort_type_enum ADD VALUE 'TopThreeMonths';
+ALTER TYPE sort_type_enum ADD VALUE 'TopSixMonths';
+ALTER TYPE sort_type_enum ADD VALUE 'TopNineMonths';
-- 
2.44.1