server/migrations/2020-02-07-210055_add_comment_subscribed/up.sql

   1
   2 -- Adding community name, hot_rank, to comment_view, user_mention_view, and subscribed to comment_view
   3
   4 -- Rebuild the comment view
   5 drop view reply_view;
   6 drop view user_mention_view;
   7 drop view user_mention_mview;
   8 drop view comment_view;
   9 drop view comment_mview;
  10 drop materialized view comment_aggregates_mview;
  11 drop view comment_aggregates_view;
  12
  13 -- reply and comment view
  14 create view comment_aggregates_view as
  15 select
  16 c.*,
  17 (select community_id from post p where p.id = c.post_id),
  18 (select co.name from post p, community co where p.id = c.post_id and p.community_id = co.id) as community_name,
  19 (select u.banned from user_ u where c.creator_id = u.id) as banned,
  20 (select cb.id::bool from community_user_ban cb, post p where c.creator_id = cb.user_id and p.id = c.post_id and p.community_id = cb.community_id) as banned_from_community,
  21 (select name from user_ where c.creator_id = user_.id) as creator_name,
  22 (select avatar from user_ where c.creator_id = user_.id) as creator_avatar,
  23 coalesce(sum(cl.score), 0) as score,
  24 count (case when cl.score = 1 then 1 else null end) as upvotes,
  25 count (case when cl.score = -1 then 1 else null end) as downvotes,
  26 hot_rank(coalesce(sum(cl.score) , 0), c.published) as hot_rank
  27 from comment c
  28 left join comment_like cl on c.id = cl.comment_id
  29 group by c.id;
  30
  31 create materialized view comment_aggregates_mview as select * from comment_aggregates_view;
  32
  33 create unique index idx_comment_aggregates_mview_id on comment_aggregates_mview (id);
  34
  35 create view comment_view as
  36 with all_comment as
  37 (
  38   select
  39   ca.*
  40   from comment_aggregates_view ca
  41 )
  42
  43 select
  44 ac.*,
  45 u.id as user_id,
  46 coalesce(cl.score, 0) as my_vote,
  47 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.community_id = cf.community_id) as subscribed,
  48 (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved
  49 from user_ u
  50 cross join all_comment ac
  51 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
  52
  53 union all
  54
  55 select
  56     ac.*,
  57     null as user_id,
  58     null as my_vote,
  59     null as subscribed,
  60     null as saved
  61 from all_comment ac
  62 ;
  63
  64 create view comment_mview as
  65 with all_comment as
  66 (
  67   select
  68   ca.*
  69   from comment_aggregates_mview ca
  70 )
  71
  72 select
  73 ac.*,
  74 u.id as user_id,
  75 coalesce(cl.score, 0) as my_vote,
  76 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.community_id = cf.community_id) as subscribed,
  77 (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved
  78 from user_ u
  79 cross join all_comment ac
  80 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
  81
  82 union all
  83
  84 select
  85     ac.*,
  86     null as user_id,
  87     null as my_vote,
  88     null as subscribed,
  89     null as saved
  90 from all_comment ac
  91 ;
  92
  93 -- Do the reply_view referencing the comment_mview
  94 create view reply_view as
  95 with closereply as (
  96     select
  97     c2.id,
  98     c2.creator_id as sender_id,
  99     c.creator_id as recipient_id
 100     from comment c
 101     inner join comment c2 on c.id = c2.parent_id
 102     where c2.creator_id != c.creator_id
 103     -- Do union where post is null
 104     union
 105     select
 106     c.id,
 107     c.creator_id as sender_id,
 108     p.creator_id as recipient_id
 109     from comment c, post p
 110     where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id
 111 )
 112 select cv.*,
 113 closereply.recipient_id
 114 from comment_mview cv, closereply
 115 where closereply.id = cv.id
 116 ;
 117
 118 -- user mention
 119 create view user_mention_view as
 120 select
 121     c.id,
 122     um.id as user_mention_id,
 123     c.creator_id,
 124     c.post_id,
 125     c.parent_id,
 126     c.content,
 127     c.removed,
 128     um.read,
 129     c.published,
 130     c.updated,
 131     c.deleted,
 132     c.community_id,
 133     c.community_name,
 134     c.banned,
 135     c.banned_from_community,
 136     c.creator_name,
 137     c.creator_avatar,
 138     c.score,
 139     c.upvotes,
 140     c.downvotes,
 141     c.hot_rank,
 142     c.user_id,
 143     c.my_vote,
 144     c.saved,
 145     um.recipient_id
 146 from user_mention um, comment_view c
 147 where um.comment_id = c.id;
 148
 149
 150 create view user_mention_mview as
 151 with all_comment as
 152 (
 153   select
 154   ca.*
 155   from comment_aggregates_mview ca
 156 )
 157
 158 select
 159     ac.id,
 160     um.id as user_mention_id,
 161     ac.creator_id,
 162     ac.post_id,
 163     ac.parent_id,
 164     ac.content,
 165     ac.removed,
 166     um.read,
 167     ac.published,
 168     ac.updated,
 169     ac.deleted,
 170     ac.community_id,
 171     ac.community_name,
 172     ac.banned,
 173     ac.banned_from_community,
 174     ac.creator_name,
 175     ac.creator_avatar,
 176     ac.score,
 177     ac.upvotes,
 178     ac.downvotes,
 179     ac.hot_rank,
 180     u.id as user_id,
 181     coalesce(cl.score, 0) as my_vote,
 182     (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
 183     um.recipient_id
 184 from user_ u
 185 cross join all_comment ac
 186 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
 187 left join user_mention um on um.comment_id = ac.id
 188
 189 union all
 190
 191 select
 192     ac.id,
 193     um.id as user_mention_id,
 194     ac.creator_id,
 195     ac.post_id,
 196     ac.parent_id,
 197     ac.content,
 198     ac.removed,
 199     um.read,
 200     ac.published,
 201     ac.updated,
 202     ac.deleted,
 203     ac.community_id,
 204     ac.community_name,
 205     ac.banned,
 206     ac.banned_from_community,
 207     ac.creator_name,
 208     ac.creator_avatar,
 209     ac.score,
 210     ac.upvotes,
 211     ac.downvotes,
 212     ac.hot_rank,
 213     null as user_id,
 214     null as my_vote,
 215     null as saved,
 216     um.recipient_id
 217 from all_comment ac
 218 left join user_mention um on um.comment_id = ac.id
 219 ;
 220