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

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