migrations/2019-09-05-230317_add_mod_ban_views/down.sql

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