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