migrations/2020-03-06-202329_add_post_iframely_data/down.sql

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