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 (
   4   select
   5   ca.*
   6   from comment_aggregates_mview ca
   7 )
   8
   9 select
  10     ac.id,
  11     um.id as user_mention_id,
  12     ac.creator_id,
  13     ac.post_id,
  14     ac.parent_id,
  15     ac.content,
  16     ac.removed,
  17     um.read,
  18     ac.published,
  19     ac.updated,
  20     ac.deleted,
  21     ac.community_id,
  22     ac.banned,
  23     ac.banned_from_community,
  24     ac.creator_name,
  25     ac.creator_avatar,
  26     ac.score,
  27     ac.upvotes,
  28     ac.downvotes,
  29     u.id as user_id,
  30     coalesce(cl.score, 0) as my_vote,
  31     (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
  32     um.recipient_id
  33 from user_ u
  34 cross join all_comment ac
  35 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
  36 left join user_mention um on um.comment_id = ac.id
  37
  38 union all
  39
  40 select
  41     ac.id,
  42     um.id as user_mention_id,
  43     ac.creator_id,
  44     ac.post_id,
  45     ac.parent_id,
  46     ac.content,
  47     ac.removed,
  48     um.read,
  49     ac.published,
  50     ac.updated,
  51     ac.deleted,
  52     ac.community_id,
  53     ac.banned,
  54     ac.banned_from_community,
  55     ac.creator_name,
  56     ac.creator_avatar,
  57     ac.score,
  58     ac.upvotes,
  59     ac.downvotes,
  60     null as user_id,
  61     null as my_vote,
  62     null as saved,
  63     um.recipient_id
  64 from all_comment ac
  65 left join user_mention um on um.comment_id = ac.id
  66 ;
  67