Mostly working, before merge

2 files changed, 26 insertions, 0 deletions

diff --git a/server/migrations/2019-04-03-155309_create_comment_view/down.sql b/server/migrations/2019-04-03-155309_create_comment_view/down.sql
index 2da934a4..c19d5ff7 100644
--- a/server/migrations/2019-04-03-155309_create_comment_view/down.sql
+++ b/server/migrations/2019-04-03-155309_create_comment_view/down.sql
@@ -1 +1,2 @@
+drop view reply_view;
 drop view comment_view;
diff --git a/server/migrations/2019-04-03-155309_create_comment_view/up.sql b/server/migrations/2019-04-03-155309_create_comment_view/up.sql
index a78e3ac3..24ce98fc 100644
--- a/server/migrations/2019-04-03-155309_create_comment_view/up.sql
+++ b/server/migrations/2019-04-03-155309_create_comment_view/up.sql
@@ -33,3 +33,28 @@ select
     null as saved
 from all_comment ac
 ;
+
+create view reply_view as 
+with closereply as (
+    select 
+    c2.id, 
+    c2.creator_id as sender_id, 
+    c.creator_id as recipient_id
+    from comment c
+    inner join comment c2 on c.id = c2.parent_id
+    where c2.creator_id != c.creator_id
+    -- Do union where post is null
+    union
+    select
+    c.id,
+    c.creator_id as sender_id,
+    p.creator_id as recipient_id
+    from comment c, post p
+    where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id
+)
+select cv.*,
+closereply.recipient_id
+from comment_view cv, closereply
+where closereply.id = cv.id
+;
+