From f5bef3980a4e40a0148fbf32071a96bd22116f3a Mon Sep 17 00:00:00 2001 From: Dessalines <tyhou13@gmx.com> Date: Sun, 6 Dec 2020 16:44:36 -0500 Subject: [PATCH] Adding hot rank function, possibly fixing views. --- lemmy_db/src/lib.rs | 8 ++++++++ lemmy_db/src/views/community_view.rs | 5 +++-- .../2020-12-03-035643_create_user_aggregates/up.sql | 8 ++++---- .../2020-12-04-183345_create_community_aggregates/up.sql | 4 ++-- 4 files changed, 17 insertions(+), 8 deletions(-) diff --git a/lemmy_db/src/lib.rs b/lemmy_db/src/lib.rs index 9fb43d6e..b5348a6c 100644 --- a/lemmy_db/src/lib.rs +++ b/lemmy_db/src/lib.rs @@ -217,6 +217,14 @@ lazy_static! { Regex::new(r"^[a-zA-Z0-9.!#$%&â*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)*$").unwrap(); } +pub(crate) mod functions { + use diesel::sql_types::*; + + sql_function! { + fn hot_rank(score: BigInt, time: Timestamp) -> Integer; + } +} + #[cfg(test)] mod tests { use super::fuzzy_search; diff --git a/lemmy_db/src/views/community_view.rs b/lemmy_db/src/views/community_view.rs index 2ab351f4..cbb90a42 100644 --- a/lemmy_db/src/views/community_view.rs +++ b/lemmy_db/src/views/community_view.rs @@ -2,6 +2,7 @@ use crate::{ aggregates::community_aggregates::CommunityAggregates, category::Category, community::{Community, CommunityFollower, CommunitySafe}, + functions::hot_rank, fuzzy_search, limit_and_offset, schema::{category, community, community_aggregates, community_follower, user_}, @@ -253,8 +254,8 @@ impl<'a> CommunityQueryBuilder<'a> { // Covers all other sorts, including hot _ => { query = query - // TODO do custom sql function for hot_rank - // .order_by(hot_rank.desc()) + // TODO do custom sql function for hot_rank, make sure this works + .order_by(hot_rank(community_aggregates::subscribers, community::published).desc()) .then_order_by(community_aggregates::subscribers.desc()) } }; diff --git a/migrations/2020-12-03-035643_create_user_aggregates/up.sql b/migrations/2020-12-03-035643_create_user_aggregates/up.sql index 8d46fbfb..bc7e6394 100644 --- a/migrations/2020-12-03-035643_create_user_aggregates/up.sql +++ b/migrations/2020-12-03-035643_create_user_aggregates/up.sql @@ -42,10 +42,10 @@ as $$ begin IF (TG_OP = 'INSERT') THEN update user_aggregates - set post_count = post_count + 1 where user_id = NEW.user_id; + set post_count = post_count + 1 where user_id = NEW.creator_id; ELSIF (TG_OP = 'DELETE') THEN update user_aggregates - set post_count = post_count - 1 where user_id = OLD.user_id; + set post_count = post_count - 1 where user_id = OLD.creator_id; END IF; return null; end $$; @@ -86,10 +86,10 @@ as $$ begin IF (TG_OP = 'INSERT') THEN update user_aggregates - set comment_count = comment_count + 1 where user_id = NEW.user_id; + set comment_count = comment_count + 1 where user_id = NEW.creator_id; ELSIF (TG_OP = 'DELETE') THEN update user_aggregates - set comment_count = comment_count - 1 where user_id = OLD.user_id; + set comment_count = comment_count - 1 where user_id = OLD.creator_id; END IF; return null; end $$; diff --git a/migrations/2020-12-04-183345_create_community_aggregates/up.sql b/migrations/2020-12-04-183345_create_community_aggregates/up.sql index 8af01597..ec951819 100644 --- a/migrations/2020-12-04-183345_create_community_aggregates/up.sql +++ b/migrations/2020-12-04-183345_create_community_aggregates/up.sql @@ -59,10 +59,10 @@ as $$ begin IF (TG_OP = 'INSERT') THEN update community_aggregates - set comments = comments + 1 where community_id = NEW.community_id; + set comments = comments + 1 from comment c join post p on p.id = c.post_id and p.id = NEW.post_id; ELSIF (TG_OP = 'DELETE') THEN update community_aggregates - set comments = comments - 1 where community_id = OLD.community_id; + set comments = comments - 1 from comment c join post p on p.id = c.post_id and p.id = OLD.post_id; END IF; return null; end $$; -- 2.44.1