1 files changed, 25 insertions, 0 deletions
diff --git a/server/migrations/2020-01-29-011901_create_reply_materialized_view/down.sql b/server/migrations/2020-01-29-011901_create_reply_materialized_view/down.sql
new file mode 100644
index 00000000..06ec5971
--- /dev/null
+++ b/server/migrations/2020-01-29-011901_create_reply_materialized_view/down.sql
@@ -0,0 +1,25 @@
+-- Drop the materialized / built views
+drop view reply_view;
+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
+;