From 54d660ad883e96d6d64f0e1104a2a1db63dcbd82 Mon Sep 17 00:00:00 2001
From: Dessalines <tyhou13@gmx.com>
Date: Sat, 30 Mar 2019 17:08:07 -0700
Subject: [PATCH] Adding hot ranking in sql, post listing view

---
 .../2019-03-03-163336_create_post/down.sql    |  2 ++
 .../2019-03-03-163336_create_post/up.sql      |  4 +++-
 .../2019-03-30-212058_post_listing/down.sql   |  2 ++
 .../2019-03-30-212058_post_listing/up.sql     | 20 +++++++++++++++++++
 server/src/actions/comment.rs                 |  2 +-
 ui/src/components/post.tsx                    |  1 -
 6 files changed, 28 insertions(+), 3 deletions(-)
 create mode 100644 server/migrations/2019-03-30-212058_post_listing/down.sql
 create mode 100644 server/migrations/2019-03-30-212058_post_listing/up.sql

diff --git a/server/migrations/2019-03-03-163336_create_post/down.sql b/server/migrations/2019-03-03-163336_create_post/down.sql
index acc0b5d1..d3ffa6b9 100644
--- a/server/migrations/2019-03-03-163336_create_post/down.sql
+++ b/server/migrations/2019-03-03-163336_create_post/down.sql
@@ -1,2 +1,4 @@
+drop function hot_rank;
+drop view post_listing;
 drop table post_like;
 drop table post;
diff --git a/server/migrations/2019-03-03-163336_create_post/up.sql b/server/migrations/2019-03-03-163336_create_post/up.sql
index 14294c8f..2cb8bb01 100644
--- a/server/migrations/2019-03-03-163336_create_post/up.sql
+++ b/server/migrations/2019-03-03-163336_create_post/up.sql
@@ -14,5 +14,7 @@ create table post_like (
   post_id int references post on update cascade on delete cascade not null,
   fedi_user_id text not null,
   score smallint not null, -- -1, or 1 for dislike, like, no row for no opinion
-  published timestamp not null default now()
+  published timestamp not null default now(),
+  unique(post_id, fedi_user_id)
 );
+
diff --git a/server/migrations/2019-03-30-212058_post_listing/down.sql b/server/migrations/2019-03-30-212058_post_listing/down.sql
new file mode 100644
index 00000000..379ce34e
--- /dev/null
+++ b/server/migrations/2019-03-30-212058_post_listing/down.sql
@@ -0,0 +1,2 @@
+drop view post_listing;
+drop function hot_rank;
diff --git a/server/migrations/2019-03-30-212058_post_listing/up.sql b/server/migrations/2019-03-30-212058_post_listing/up.sql
new file mode 100644
index 00000000..1796c8f6
--- /dev/null
+++ b/server/migrations/2019-03-30-212058_post_listing/up.sql
@@ -0,0 +1,20 @@
+-- Rank = ScaleFactor * sign(Score) * log(1 + abs(Score)) / (Time + 2)^Gravity
+create or replace function hot_rank(
+  score numeric,
+  published timestamp without time zone)
+returns numeric as $$
+begin
+  -- hours_diff:=EXTRACT(EPOCH FROM (timezone('utc',now()) - published))/3600
+  return 10000*sign(score)*log(1 + abs(score)) / power(((EXTRACT(EPOCH FROM (timezone('utc',now()) - published))/3600) + 2), 1.8);
+end; $$
+LANGUAGE plpgsql;
+
+create view post_listing as 
+select post.*, 
+(select count(*) from comment where comment.post_id = post.id) as number_of_comments,
+coalesce(sum(post_like.score),0) as score,
+hot_rank(coalesce(sum(post_like.score),0), post.published) as hot_rank
+from post
+left join post_like
+on post.id = post_like.post_id
+group by post.id;
diff --git a/server/src/actions/comment.rs b/server/src/actions/comment.rs
index 089c384c..14f6e931 100644
--- a/server/src/actions/comment.rs
+++ b/server/src/actions/comment.rs
@@ -152,7 +152,7 @@ impl CommentView {
 
     for like in likes.iter() {
       if like.score == 1 {
-        upvotes += 1
+        upvotes += 1;
       } else if like.score == -1 {
         downvotes += 1;
       }
diff --git a/ui/src/components/post.tsx b/ui/src/components/post.tsx
index 1d9412fc..adb90840 100644
--- a/ui/src/components/post.tsx
+++ b/ui/src/components/post.tsx
@@ -424,7 +424,6 @@ export class CommentForm extends Component<CommentFormProps, CommentFormState> {
     }  
   }
 
-
   render() {
     return (
       <div>
-- 
2.44.1