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

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