migrations/2019-09-09-042010_add_stickied_posts/up.sql

   1 -- Add the column
   2 ALTER TABLE post
   3     ADD COLUMN stickied boolean DEFAULT FALSE NOT NULL;
   4
   5 -- Add the mod table
   6 CREATE TABLE mod_sticky_post (
   7     id serial PRIMARY KEY,
   8     mod_user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
   9     post_id int REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
  10     stickied boolean DEFAULT TRUE,
  11     when_ timestamp NOT NULL DEFAULT now()
  12 );
  13
  14 -- Add mod view
  15 CREATE VIEW mod_sticky_post_view AS
  16 SELECT
  17     msp.*,
  18     (
  19         SELECT
  20             name
  21         FROM
  22             user_ u
  23         WHERE
  24             msp.mod_user_id = u.id) AS mod_user_name,
  25     (
  26         SELECT
  27             name
  28         FROM
  29             post p
  30         WHERE
  31             msp.post_id = p.id) AS post_name,
  32     (
  33         SELECT
  34             c.id
  35         FROM
  36             post p,
  37             community c
  38         WHERE
  39             msp.post_id = p.id
  40             AND p.community_id = c.id) AS community_id,
  41     (
  42         SELECT
  43             c.name
  44         FROM
  45             post p,
  46             community c
  47         WHERE
  48             msp.post_id = p.id
  49             AND p.community_id = c.id) AS community_name
  50 FROM
  51     mod_sticky_post msp;
  52
  53 -- Recreate the view
  54 DROP VIEW post_view;
  55
  56 CREATE VIEW post_view AS
  57 with all_post AS (
  58     SELECT
  59         p.*,
  60         (
  61             SELECT
  62                 u.banned
  63             FROM
  64                 user_ u
  65             WHERE
  66                 p.creator_id = u.id) AS banned,
  67         (
  68             SELECT
  69                 cb.id::bool
  70             FROM
  71                 community_user_ban cb
  72             WHERE
  73                 p.creator_id = cb.user_id
  74                 AND p.community_id = cb.community_id) AS banned_from_community,
  75         (
  76             SELECT
  77                 name
  78             FROM
  79                 user_
  80             WHERE
  81                 p.creator_id = user_.id) AS creator_name,
  82         (
  83             SELECT
  84                 name
  85             FROM
  86                 community
  87             WHERE
  88                 p.community_id = community.id) AS community_name,
  89         (
  90             SELECT
  91                 removed
  92             FROM
  93                 community c
  94             WHERE
  95                 p.community_id = c.id) AS community_removed,
  96         (
  97             SELECT
  98                 deleted
  99             FROM
 100                 community c
 101             WHERE
 102                 p.community_id = c.id) AS community_deleted,
 103         (
 104             SELECT
 105                 nsfw
 106             FROM
 107                 community c
 108             WHERE
 109                 p.community_id = c.id) AS community_nsfw,
 110         (
 111             SELECT
 112                 count(*)
 113             FROM
 114                 comment
 115             WHERE
 116                 comment.post_id = p.id) AS number_of_comments,
 117         coalesce(sum(pl.score), 0) AS score,
 118         count(
 119             CASE WHEN pl.score = 1 THEN
 120                 1
 121             ELSE
 122                 NULL
 123             END) AS upvotes,
 124         count(
 125             CASE WHEN pl.score = - 1 THEN
 126                 1
 127             ELSE
 128                 NULL
 129             END) AS downvotes,
 130         hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
 131     FROM
 132         post p
 133         LEFT JOIN post_like pl ON p.id = pl.post_id
 134     GROUP BY
 135         p.id
 136 )
 137 SELECT
 138     ap.*,
 139     u.id AS user_id,
 140     coalesce(pl.score, 0) AS my_vote,
 141     (
 142         SELECT
 143             cf.id::bool
 144         FROM
 145             community_follower cf
 146         WHERE
 147             u.id = cf.user_id
 148             AND cf.community_id = ap.community_id) AS subscribed,
 149     (
 150         SELECT
 151             pr.id::bool
 152         FROM
 153             post_read pr
 154         WHERE
 155             u.id = pr.user_id
 156             AND pr.post_id = ap.id) AS read,
 157     (
 158         SELECT
 159             ps.id::bool
 160         FROM
 161             post_saved ps
 162         WHERE
 163             u.id = ps.user_id
 164             AND ps.post_id = ap.id) AS saved
 165 FROM
 166     user_ u
 167     CROSS JOIN all_post ap
 168     LEFT JOIN post_like pl ON u.id = pl.user_id
 169         AND ap.id = pl.post_id
 170     UNION ALL
 171     SELECT
 172         ap.*,
 173         NULL AS user_id,
 174         NULL AS my_vote,
 175         NULL AS subscribed,
 176         NULL AS read,
 177         NULL AS saved
 178     FROM
 179         all_post ap;
 180