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

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