migrations/2019-04-29-175834_add_delete_columns/down.sql

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