migrations/2020-01-29-011901_create_reply_materialized_view/up.sql

   1 -- https://github.com/dessalines/lemmy/issues/197
   2 DROP VIEW reply_view;
   3
   4 -- Do the reply_view referencing the comment_mview
   5 CREATE VIEW reply_view AS
   6 with closereply AS (
   7     SELECT
   8         c2.id,
   9         c2.creator_id AS sender_id,
  10         c.creator_id AS recipient_id
  11     FROM
  12         comment c
  13         INNER JOIN comment c2 ON c.id = c2.parent_id
  14     WHERE
  15         c2.creator_id != c.creator_id
  16         -- Do union where post is null
  17     UNION
  18     SELECT
  19         c.id,
  20         c.creator_id AS sender_id,
  21         p.creator_id AS recipient_id
  22     FROM
  23         comment c,
  24         post p
  25     WHERE
  26         c.post_id = p.id
  27         AND c.parent_id IS NULL
  28         AND c.creator_id != p.creator_id
  29 )
  30 SELECT
  31     cv.*,
  32     closereply.recipient_id
  33 FROM
  34     comment_mview cv,
  35     closereply
  36 WHERE
  37     closereply.id = cv.id;
  38