migrations/2019-08-11-000918_add_nsfw_columns/down.sql

   1 DROP VIEW community_view;
   2
   3 DROP VIEW post_view;
   4
   5 ALTER TABLE community
   6     DROP COLUMN nsfw;
   7
   8 ALTER TABLE post
   9     DROP COLUMN nsfw;
  10
  11 ALTER TABLE user_
  12     DROP COLUMN show_nsfw;
  13
  14 -- the views
  15 CREATE VIEW community_view AS
  16 with all_community AS (
  17     SELECT
  18         *,
  19         (
  20             SELECT
  21                 name
  22             FROM
  23                 user_ u
  24             WHERE
  25                 c.creator_id = u.id) AS creator_name,
  26         (
  27             SELECT
  28                 name
  29             FROM
  30                 category ct
  31             WHERE
  32                 c.category_id = ct.id) AS category_name,
  33         (
  34             SELECT
  35                 count(*)
  36             FROM
  37                 community_follower cf
  38             WHERE
  39                 cf.community_id = c.id) AS number_of_subscribers,
  40         (
  41             SELECT
  42                 count(*)
  43             FROM
  44                 post p
  45             WHERE
  46                 p.community_id = c.id) AS number_of_posts,
  47         (
  48             SELECT
  49                 count(*)
  50             FROM
  51                 comment co,
  52                 post p
  53             WHERE
  54                 c.id = p.community_id
  55                 AND p.id = co.post_id) AS number_of_comments,
  56         hot_rank ((
  57             SELECT
  58                 count(*)
  59             FROM community_follower cf
  60             WHERE
  61                 cf.community_id = c.id), c.published) AS hot_rank
  62 FROM
  63     community c
  64 )
  65 SELECT
  66     ac.*,
  67     u.id AS user_id,
  68     (
  69         SELECT
  70             cf.id::boolean
  71         FROM
  72             community_follower cf
  73         WHERE
  74             u.id = cf.user_id
  75             AND ac.id = cf.community_id) AS subscribed
  76 FROM
  77     user_ u
  78     CROSS JOIN all_community ac
  79 UNION ALL
  80 SELECT
  81     ac.*,
  82     NULL AS user_id,
  83     NULL AS subscribed
  84 FROM
  85     all_community ac;
  86
  87 -- Post view
  88 CREATE VIEW post_view AS
  89 with all_post AS (
  90     SELECT
  91         p.*,
  92         (
  93             SELECT
  94                 name
  95             FROM
  96                 user_
  97             WHERE
  98                 p.creator_id = user_.id) AS creator_name,
  99         (
 100             SELECT
 101                 name
 102             FROM
 103                 community
 104             WHERE
 105                 p.community_id = community.id) AS community_name,
 106         (
 107             SELECT
 108                 removed
 109             FROM
 110                 community c
 111             WHERE
 112                 p.community_id = c.id) AS community_removed,
 113         (
 114             SELECT
 115                 deleted
 116             FROM
 117                 community c
 118             WHERE
 119                 p.community_id = c.id) AS community_deleted,
 120         (
 121             SELECT
 122                 count(*)
 123             FROM
 124                 comment
 125             WHERE
 126                 comment.post_id = p.id) AS number_of_comments,
 127         coalesce(sum(pl.score), 0) AS score,
 128         count(
 129             CASE WHEN pl.score = 1 THEN
 130                 1
 131             ELSE
 132                 NULL
 133             END) AS upvotes,
 134         count(
 135             CASE WHEN pl.score = - 1 THEN
 136                 1
 137             ELSE
 138                 NULL
 139             END) AS downvotes,
 140         hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
 141     FROM
 142         post p
 143         LEFT JOIN post_like pl ON p.id = pl.post_id
 144     GROUP BY
 145         p.id
 146 )
 147 SELECT
 148     ap.*,
 149     u.id AS user_id,
 150     coalesce(pl.score, 0) AS my_vote,
 151     (
 152         SELECT
 153             cf.id::bool
 154         FROM
 155             community_follower cf
 156         WHERE
 157             u.id = cf.user_id
 158             AND cf.community_id = ap.community_id) AS subscribed,
 159     (
 160         SELECT
 161             pr.id::bool
 162         FROM
 163             post_read pr
 164         WHERE
 165             u.id = pr.user_id
 166             AND pr.post_id = ap.id) AS read,
 167     (
 168         SELECT
 169             ps.id::bool
 170         FROM
 171             post_saved ps
 172         WHERE
 173             u.id = ps.user_id
 174             AND ps.post_id = ap.id) AS saved
 175 FROM
 176     user_ u
 177     CROSS JOIN all_post ap
 178     LEFT JOIN post_like pl ON u.id = pl.user_id
 179         AND ap.id = pl.post_id
 180     UNION ALL
 181     SELECT
 182         ap.*,
 183         NULL AS user_id,
 184         NULL AS my_vote,
 185         NULL AS subscribed,
 186         NULL AS read,
 187         NULL AS saved
 188     FROM
 189         all_post ap;
 190