migrations/2019-09-09-042010_add_stickied_posts/down.sql

   1 DROP VIEW post_view;
   2
   3 DROP VIEW mod_sticky_post_view;
   4
   5 ALTER TABLE post
   6     DROP COLUMN stickied;
   7
   8 DROP TABLE mod_sticky_post;
   9
  10 CREATE VIEW post_view AS
  11 with all_post AS (
  12     SELECT
  13         p.*,
  14         (
  15             SELECT
  16                 u.banned
  17             FROM
  18                 user_ u
  19             WHERE
  20                 p.creator_id = u.id) AS banned,
  21         (
  22             SELECT
  23                 cb.id::bool
  24             FROM
  25                 community_user_ban cb
  26             WHERE
  27                 p.creator_id = cb.user_id
  28                 AND p.community_id = cb.community_id) AS banned_from_community,
  29         (
  30             SELECT
  31                 name
  32             FROM
  33                 user_
  34             WHERE
  35                 p.creator_id = user_.id) AS creator_name,
  36         (
  37             SELECT
  38                 name
  39             FROM
  40                 community
  41             WHERE
  42                 p.community_id = community.id) AS community_name,
  43         (
  44             SELECT
  45                 removed
  46             FROM
  47                 community c
  48             WHERE
  49                 p.community_id = c.id) AS community_removed,
  50         (
  51             SELECT
  52                 deleted
  53             FROM
  54                 community c
  55             WHERE
  56                 p.community_id = c.id) AS community_deleted,
  57         (
  58             SELECT
  59                 nsfw
  60             FROM
  61                 community c
  62             WHERE
  63                 p.community_id = c.id) AS community_nsfw,
  64         (
  65             SELECT
  66                 count(*)
  67             FROM
  68                 comment
  69             WHERE
  70                 comment.post_id = p.id) AS number_of_comments,
  71         coalesce(sum(pl.score), 0) AS score,
  72         count(
  73             CASE WHEN pl.score = 1 THEN
  74                 1
  75             ELSE
  76                 NULL
  77             END) AS upvotes,
  78         count(
  79             CASE WHEN pl.score = - 1 THEN
  80                 1
  81             ELSE
  82                 NULL
  83             END) AS downvotes,
  84         hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
  85     FROM
  86         post p
  87         LEFT JOIN post_like pl ON p.id = pl.post_id
  88     GROUP BY
  89         p.id
  90 )
  91 SELECT
  92     ap.*,
  93     u.id AS user_id,
  94     coalesce(pl.score, 0) AS my_vote,
  95     (
  96         SELECT
  97             cf.id::bool
  98         FROM
  99             community_follower cf
 100         WHERE
 101             u.id = cf.user_id
 102             AND cf.community_id = ap.community_id) AS subscribed,
 103     (
 104         SELECT
 105             pr.id::bool
 106         FROM
 107             post_read pr
 108         WHERE
 109             u.id = pr.user_id
 110             AND pr.post_id = ap.id) AS read,
 111     (
 112         SELECT
 113             ps.id::bool
 114         FROM
 115             post_saved ps
 116         WHERE
 117             u.id = ps.user_id
 118             AND ps.post_id = ap.id) AS saved
 119 FROM
 120     user_ u
 121     CROSS JOIN all_post ap
 122     LEFT JOIN post_like pl ON u.id = pl.user_id
 123         AND ap.id = pl.post_id
 124     UNION ALL
 125     SELECT
 126         ap.*,
 127         NULL AS user_id,
 128         NULL AS my_vote,
 129         NULL AS subscribed,
 130         NULL AS read,
 131         NULL AS saved
 132     FROM
 133         all_post ap;
 134