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

   1 -- Adding community name, hot_rank, to comment_view, user_mention_view, and subscribed to comment_view
   2 -- Rebuild the comment view
   3 DROP VIEW reply_view;
   4
   5 DROP VIEW user_mention_view;
   6
   7 DROP VIEW user_mention_mview;
   8
   9 DROP VIEW comment_view;
  10
  11 DROP VIEW comment_mview;
  12
  13 DROP MATERIALIZED VIEW comment_aggregates_mview;
  14
  15 DROP VIEW comment_aggregates_view;
  16
  17 -- reply and comment view
  18 CREATE VIEW comment_aggregates_view AS
  19 SELECT
  20     c.*,
  21     (
  22         SELECT
  23             community_id
  24         FROM
  25             post p
  26         WHERE
  27             p.id = c.post_id), (
  28         SELECT
  29             co.name
  30         FROM
  31             post p,
  32             community co
  33         WHERE
  34             p.id = c.post_id
  35             AND p.community_id = co.id) AS community_name,
  36     (
  37         SELECT
  38             u.banned
  39         FROM
  40             user_ u
  41         WHERE
  42             c.creator_id = u.id) AS banned,
  43     (
  44         SELECT
  45             cb.id::bool
  46         FROM
  47             community_user_ban cb,
  48             post p
  49         WHERE
  50             c.creator_id = cb.user_id
  51             AND p.id = c.post_id
  52             AND p.community_id = cb.community_id) AS banned_from_community,
  53     (
  54         SELECT
  55             name
  56         FROM
  57             user_
  58         WHERE
  59             c.creator_id = user_.id) AS creator_name,
  60     (
  61         SELECT
  62             avatar
  63         FROM
  64             user_
  65         WHERE
  66             c.creator_id = user_.id) AS creator_avatar,
  67     coalesce(sum(cl.score), 0) AS score,
  68     count(
  69         CASE WHEN cl.score = 1 THEN
  70             1
  71         ELSE
  72             NULL
  73         END) AS upvotes,
  74     count(
  75         CASE WHEN cl.score = - 1 THEN
  76             1
  77         ELSE
  78             NULL
  79         END) AS downvotes,
  80     hot_rank (coalesce(sum(cl.score), 0), c.published) AS hot_rank
  81 FROM
  82     comment c
  83     LEFT JOIN comment_like cl ON c.id = cl.comment_id
  84 GROUP BY
  85     c.id;
  86
  87 CREATE MATERIALIZED VIEW comment_aggregates_mview AS
  88 SELECT
  89     *
  90 FROM
  91     comment_aggregates_view;
  92
  93 CREATE UNIQUE INDEX idx_comment_aggregates_mview_id ON comment_aggregates_mview (id);
  94
  95 CREATE VIEW comment_view AS
  96 with all_comment AS (
  97     SELECT
  98         ca.*
  99     FROM
 100         comment_aggregates_view ca
 101 )
 102 SELECT
 103     ac.*,
 104     u.id AS user_id,
 105     coalesce(cl.score, 0) AS my_vote,
 106     (
 107         SELECT
 108             cf.id::boolean
 109         FROM
 110             community_follower cf
 111         WHERE
 112             u.id = cf.user_id
 113             AND ac.community_id = cf.community_id) AS subscribed,
 114     (
 115         SELECT
 116             cs.id::bool
 117         FROM
 118             comment_saved cs
 119         WHERE
 120             u.id = cs.user_id
 121             AND cs.comment_id = ac.id) AS saved
 122 FROM
 123     user_ u
 124     CROSS JOIN all_comment ac
 125     LEFT JOIN comment_like cl ON u.id = cl.user_id
 126         AND ac.id = cl.comment_id
 127     UNION ALL
 128     SELECT
 129         ac.*,
 130         NULL AS user_id,
 131         NULL AS my_vote,
 132         NULL AS subscribed,
 133         NULL AS saved
 134     FROM
 135         all_comment ac;
 136
 137 CREATE VIEW comment_mview AS
 138 with all_comment AS (
 139     SELECT
 140         ca.*
 141     FROM
 142         comment_aggregates_mview ca
 143 )
 144 SELECT
 145     ac.*,
 146     u.id AS user_id,
 147     coalesce(cl.score, 0) AS my_vote,
 148     (
 149         SELECT
 150             cf.id::boolean
 151         FROM
 152             community_follower cf
 153         WHERE
 154             u.id = cf.user_id
 155             AND ac.community_id = cf.community_id) AS subscribed,
 156     (
 157         SELECT
 158             cs.id::bool
 159         FROM
 160             comment_saved cs
 161         WHERE
 162             u.id = cs.user_id
 163             AND cs.comment_id = ac.id) AS saved
 164 FROM
 165     user_ u
 166     CROSS JOIN all_comment ac
 167     LEFT JOIN comment_like cl ON u.id = cl.user_id
 168         AND ac.id = cl.comment_id
 169     UNION ALL
 170     SELECT
 171         ac.*,
 172         NULL AS user_id,
 173         NULL AS my_vote,
 174         NULL AS subscribed,
 175         NULL AS saved
 176     FROM
 177         all_comment ac;
 178
 179 -- Do the reply_view referencing the comment_mview
 180 CREATE VIEW reply_view AS
 181 with closereply AS (
 182     SELECT
 183         c2.id,
 184         c2.creator_id AS sender_id,
 185         c.creator_id AS recipient_id
 186     FROM
 187         comment c
 188         INNER JOIN comment c2 ON c.id = c2.parent_id
 189     WHERE
 190         c2.creator_id != c.creator_id
 191         -- Do union where post is null
 192     UNION
 193     SELECT
 194         c.id,
 195         c.creator_id AS sender_id,
 196         p.creator_id AS recipient_id
 197     FROM
 198         comment c,
 199         post p
 200     WHERE
 201         c.post_id = p.id
 202         AND c.parent_id IS NULL
 203         AND c.creator_id != p.creator_id
 204 )
 205 SELECT
 206     cv.*,
 207     closereply.recipient_id
 208 FROM
 209     comment_mview cv,
 210     closereply
 211 WHERE
 212     closereply.id = cv.id;
 213
 214 -- user mention
 215 CREATE VIEW user_mention_view AS
 216 SELECT
 217     c.id,
 218     um.id AS user_mention_id,
 219     c.creator_id,
 220     c.post_id,
 221     c.parent_id,
 222     c.content,
 223     c.removed,
 224     um.read,
 225     c.published,
 226     c.updated,
 227     c.deleted,
 228     c.community_id,
 229     c.community_name,
 230     c.banned,
 231     c.banned_from_community,
 232     c.creator_name,
 233     c.creator_avatar,
 234     c.score,
 235     c.upvotes,
 236     c.downvotes,
 237     c.hot_rank,
 238     c.user_id,
 239     c.my_vote,
 240     c.saved,
 241     um.recipient_id
 242 FROM
 243     user_mention um,
 244     comment_view c
 245 WHERE
 246     um.comment_id = c.id;
 247
 248 CREATE VIEW user_mention_mview AS
 249 with all_comment AS (
 250     SELECT
 251         ca.*
 252     FROM
 253         comment_aggregates_mview ca
 254 )
 255 SELECT
 256     ac.id,
 257     um.id AS user_mention_id,
 258     ac.creator_id,
 259     ac.post_id,
 260     ac.parent_id,
 261     ac.content,
 262     ac.removed,
 263     um.read,
 264     ac.published,
 265     ac.updated,
 266     ac.deleted,
 267     ac.community_id,
 268     ac.community_name,
 269     ac.banned,
 270     ac.banned_from_community,
 271     ac.creator_name,
 272     ac.creator_avatar,
 273     ac.score,
 274     ac.upvotes,
 275     ac.downvotes,
 276     ac.hot_rank,
 277     u.id AS user_id,
 278     coalesce(cl.score, 0) AS my_vote,
 279     (
 280         SELECT
 281             cs.id::bool
 282         FROM
 283             comment_saved cs
 284         WHERE
 285             u.id = cs.user_id
 286             AND cs.comment_id = ac.id) AS saved,
 287     um.recipient_id
 288 FROM
 289     user_ u
 290     CROSS JOIN all_comment ac
 291     LEFT JOIN comment_like cl ON u.id = cl.user_id
 292         AND ac.id = cl.comment_id
 293     LEFT JOIN user_mention um ON um.comment_id = ac.id
 294 UNION ALL
 295 SELECT
 296     ac.id,
 297     um.id AS user_mention_id,
 298     ac.creator_id,
 299     ac.post_id,
 300     ac.parent_id,
 301     ac.content,
 302     ac.removed,
 303     um.read,
 304     ac.published,
 305     ac.updated,
 306     ac.deleted,
 307     ac.community_id,
 308     ac.community_name,
 309     ac.banned,
 310     ac.banned_from_community,
 311     ac.creator_name,
 312     ac.creator_avatar,
 313     ac.score,
 314     ac.upvotes,
 315     ac.downvotes,
 316     ac.hot_rank,
 317     NULL AS user_id,
 318     NULL AS my_vote,
 319     NULL AS saved,
 320     um.recipient_id
 321 FROM
 322     all_comment ac
 323     LEFT JOIN user_mention um ON um.comment_id = ac.id;
 324