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

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