From 4778f198e16ae64dc054687264d2d5222158a576 Mon Sep 17 00:00:00 2001
From: Dessalines <tyhou13@gmx.com>
Date: Tue, 28 Jan 2020 22:07:17 -0500
Subject: Adding a materialized view for reply_view. Fixes #466

---
 .../down.sql                                       | 25 ++++++++
 .../up.sql                                         | 27 +++++++++
 .../down.sql                                       |  1 +
 .../up.sql                                         | 67 ++++++++++++++++++++++
 4 files changed, 120 insertions(+)
 create mode 100644 server/migrations/2020-01-29-011901_create_reply_materialized_view/down.sql
 create mode 100644 server/migrations/2020-01-29-011901_create_reply_materialized_view/up.sql
 create mode 100644 server/migrations/2020-01-29-030825_create_user_mention_materialized_view/down.sql
 create mode 100644 server/migrations/2020-01-29-030825_create_user_mention_materialized_view/up.sql

(limited to 'server/migrations')

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
+;
diff --git a/server/migrations/2020-01-29-011901_create_reply_materialized_view/up.sql b/server/migrations/2020-01-29-011901_create_reply_materialized_view/up.sql
new file mode 100644
index 00000000..ebbb1dff
--- /dev/null
+++ b/server/migrations/2020-01-29-011901_create_reply_materialized_view/up.sql
@@ -0,0 +1,27 @@
+-- https://github.com/dessalines/lemmy/issues/197
+drop view reply_view;
+
+-- Do the reply_view referencing the comment_mview
+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_mview cv, closereply
+where closereply.id = cv.id
+;
diff --git a/server/migrations/2020-01-29-030825_create_user_mention_materialized_view/down.sql b/server/migrations/2020-01-29-030825_create_user_mention_materialized_view/down.sql
new file mode 100644
index 00000000..d93ebc2e
--- /dev/null
+++ b/server/migrations/2020-01-29-030825_create_user_mention_materialized_view/down.sql
@@ -0,0 +1 @@
+drop view user_mention_mview;
diff --git a/server/migrations/2020-01-29-030825_create_user_mention_materialized_view/up.sql b/server/migrations/2020-01-29-030825_create_user_mention_materialized_view/up.sql
new file mode 100644
index 00000000..b0ae4e9d
--- /dev/null
+++ b/server/migrations/2020-01-29-030825_create_user_mention_materialized_view/up.sql
@@ -0,0 +1,67 @@
+create view user_mention_mview as 
+with all_comment as
+(
+  select
+  ca.*
+  from comment_aggregates_mview ca
+)
+
+select
+    ac.id,
+    um.id as user_mention_id,
+    ac.creator_id,
+    ac.post_id,
+    ac.parent_id,
+    ac.content,
+    ac.removed,
+    um.read,
+    ac.published,
+    ac.updated,
+    ac.deleted,
+    ac.community_id,
+    ac.banned,
+    ac.banned_from_community,
+    ac.creator_name,
+    ac.creator_avatar,
+    ac.score,
+    ac.upvotes,
+    ac.downvotes,
+    u.id as user_id,
+    coalesce(cl.score, 0) as my_vote,
+    (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
+    um.recipient_id
+from user_ u
+cross join all_comment ac
+left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
+left join user_mention um on um.comment_id = ac.id
+
+union all
+
+select 
+    ac.id,
+    um.id as user_mention_id,
+    ac.creator_id,
+    ac.post_id,
+    ac.parent_id,
+    ac.content,
+    ac.removed,
+    um.read,
+    ac.published,
+    ac.updated,
+    ac.deleted,
+    ac.community_id,
+    ac.banned,
+    ac.banned_from_community,
+    ac.creator_name,
+    ac.creator_avatar,
+    ac.score,
+    ac.upvotes,
+    ac.downvotes,
+    null as user_id, 
+    null as my_vote,
+    null as saved,
+    um.recipient_id
+from all_comment ac
+left join user_mention um on um.comment_id = ac.id
+;
+
-- 
cgit v1.2.3