migrations/2020-02-08-145624_add_post_newest_activity_time/down.sql

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