migrations/2020-01-29-030825_create_user_mention_materialized_view/up.sql

   1 CREATE VIEW user_mention_mview AS
   2 with all_comment AS (
   3     SELECT
   4         ca.*
   5     FROM
   6         comment_aggregates_mview ca
   7 )
   8 SELECT
   9     ac.id,
  10     um.id AS user_mention_id,
  11     ac.creator_id,
  12     ac.post_id,
  13     ac.parent_id,
  14     ac.content,
  15     ac.removed,
  16     um.read,
  17     ac.published,
  18     ac.updated,
  19     ac.deleted,
  20     ac.community_id,
  21     ac.banned,
  22     ac.banned_from_community,
  23     ac.creator_name,
  24     ac.creator_avatar,
  25     ac.score,
  26     ac.upvotes,
  27     ac.downvotes,
  28     u.id AS user_id,
  29     coalesce(cl.score, 0) AS my_vote,
  30     (
  31         SELECT
  32             cs.id::bool
  33         FROM
  34             comment_saved cs
  35         WHERE
  36             u.id = cs.user_id
  37             AND cs.comment_id = ac.id) AS saved,
  38     um.recipient_id
  39 FROM
  40     user_ u
  41     CROSS JOIN all_comment ac
  42     LEFT JOIN comment_like cl ON u.id = cl.user_id
  43         AND ac.id = cl.comment_id
  44     LEFT JOIN user_mention um ON um.comment_id = ac.id
  45 UNION ALL
  46 SELECT
  47     ac.id,
  48     um.id AS user_mention_id,
  49     ac.creator_id,
  50     ac.post_id,
  51     ac.parent_id,
  52     ac.content,
  53     ac.removed,
  54     um.read,
  55     ac.published,
  56     ac.updated,
  57     ac.deleted,
  58     ac.community_id,
  59     ac.banned,
  60     ac.banned_from_community,
  61     ac.creator_name,
  62     ac.creator_avatar,
  63     ac.score,
  64     ac.upvotes,
  65     ac.downvotes,
  66     NULL AS user_id,
  67     NULL AS my_vote,
  68     NULL AS saved,
  69     um.recipient_id
  70 FROM
  71     all_comment ac
  72     LEFT JOIN user_mention um ON um.comment_id = ac.id;
  73