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

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