migrations/2020-07-12-100442_add_post_title_to_comments_view/up.sql

   1 DROP VIEW user_mention_view;
   2
   3 DROP VIEW reply_fast_view;
   4
   5 DROP VIEW comment_fast_view;
   6
   7 DROP VIEW comment_view;
   8
   9 DROP VIEW user_mention_fast_view;
  10
  11 DROP TABLE comment_aggregates_fast;
  12
  13 DROP VIEW comment_aggregates_view;
  14
  15 CREATE VIEW comment_aggregates_view AS
  16 SELECT
  17     ct.*,
  18     -- post details
  19     p."name" AS post_name,
  20     p.community_id,
  21     -- community details
  22     c.actor_id AS community_actor_id,
  23     c."local" AS community_local,
  24     c."name" AS community_name,
  25     -- creator details
  26     u.banned AS banned,
  27     coalesce(cb.id, 0)::bool AS banned_from_community,
  28     u.actor_id AS creator_actor_id,
  29     u.local AS creator_local,
  30     u.name AS creator_name,
  31     u.published AS creator_published,
  32     u.avatar AS creator_avatar,
  33     -- score details
  34     coalesce(cl.total, 0) AS score,
  35     coalesce(cl.up, 0) AS upvotes,
  36     coalesce(cl.down, 0) AS downvotes,
  37     hot_rank (coalesce(cl.total, 0), ct.published) AS hot_rank
  38 FROM
  39     comment ct
  40     LEFT JOIN post p ON ct.post_id = p.id
  41     LEFT JOIN community c ON p.community_id = c.id
  42     LEFT JOIN user_ u ON ct.creator_id = u.id
  43     LEFT JOIN community_user_ban cb ON ct.creator_id = cb.user_id
  44         AND p.id = ct.post_id
  45         AND p.community_id = cb.community_id
  46     LEFT JOIN (
  47         SELECT
  48             l.comment_id AS id,
  49             sum(l.score) AS total,
  50             count(
  51                 CASE WHEN l.score = 1 THEN
  52                     1
  53                 ELSE
  54                     NULL
  55                 END) AS up,
  56             count(
  57                 CASE WHEN l.score = - 1 THEN
  58                     1
  59                 ELSE
  60                     NULL
  61                 END) AS down
  62         FROM
  63             comment_like l
  64         GROUP BY
  65             comment_id) AS cl ON cl.id = ct.id;
  66
  67 CREATE OR REPLACE VIEW comment_view AS (
  68     SELECT
  69         cav.*,
  70         us.user_id AS user_id,
  71         us.my_vote AS my_vote,
  72         us.is_subbed::bool AS subscribed,
  73         us.is_saved::bool AS saved
  74     FROM
  75         comment_aggregates_view cav
  76     CROSS JOIN LATERAL (
  77         SELECT
  78             u.id AS user_id,
  79             coalesce(cl.score, 0) AS my_vote,
  80             coalesce(cf.id, 0) AS is_subbed,
  81             coalesce(cs.id, 0) AS is_saved
  82         FROM
  83             user_ u
  84             LEFT JOIN comment_like cl ON u.id = cl.user_id
  85                 AND cav.id = cl.comment_id
  86         LEFT JOIN comment_saved cs ON u.id = cs.user_id
  87             AND cs.comment_id = cav.id
  88     LEFT JOIN community_follower cf ON u.id = cf.user_id
  89         AND cav.community_id = cf.community_id) AS us
  90 UNION ALL
  91 SELECT
  92     cav.*,
  93     NULL AS user_id,
  94     NULL AS my_vote,
  95     NULL AS subscribed,
  96     NULL AS saved
  97 FROM
  98     comment_aggregates_view cav);
  99
 100 CREATE TABLE comment_aggregates_fast AS
 101 SELECT
 102     *
 103 FROM
 104     comment_aggregates_view;
 105
 106 ALTER TABLE comment_aggregates_fast
 107     ADD PRIMARY KEY (id);
 108
 109 CREATE VIEW comment_fast_view AS
 110 SELECT
 111     cav.*,
 112     us.user_id AS user_id,
 113     us.my_vote AS my_vote,
 114     us.is_subbed::bool AS subscribed,
 115     us.is_saved::bool AS saved
 116 FROM
 117     comment_aggregates_fast cav
 118     CROSS JOIN LATERAL (
 119         SELECT
 120             u.id AS user_id,
 121             coalesce(cl.score, 0) AS my_vote,
 122             coalesce(cf.id, 0) AS is_subbed,
 123             coalesce(cs.id, 0) AS is_saved
 124         FROM
 125             user_ u
 126             LEFT JOIN comment_like cl ON u.id = cl.user_id
 127                 AND cav.id = cl.comment_id
 128         LEFT JOIN comment_saved cs ON u.id = cs.user_id
 129             AND cs.comment_id = cav.id
 130     LEFT JOIN community_follower cf ON u.id = cf.user_id
 131         AND cav.community_id = cf.community_id) AS us
 132 UNION ALL
 133 SELECT
 134     cav.*,
 135     NULL AS user_id,
 136     NULL AS my_vote,
 137     NULL AS subscribed,
 138     NULL AS saved
 139 FROM
 140     comment_aggregates_fast cav;
 141
 142 CREATE VIEW user_mention_view AS
 143 SELECT
 144     c.id,
 145     um.id AS user_mention_id,
 146     c.creator_id,
 147     c.creator_actor_id,
 148     c.creator_local,
 149     c.post_id,
 150     c.post_name,
 151     c.parent_id,
 152     c.content,
 153     c.removed,
 154     um.read,
 155     c.published,
 156     c.updated,
 157     c.deleted,
 158     c.community_id,
 159     c.community_actor_id,
 160     c.community_local,
 161     c.community_name,
 162     c.banned,
 163     c.banned_from_community,
 164     c.creator_name,
 165     c.creator_avatar,
 166     c.score,
 167     c.upvotes,
 168     c.downvotes,
 169     c.hot_rank,
 170     c.user_id,
 171     c.my_vote,
 172     c.saved,
 173     um.recipient_id,
 174     (
 175         SELECT
 176             actor_id
 177         FROM
 178             user_ u
 179         WHERE
 180             u.id = um.recipient_id) AS recipient_actor_id,
 181     (
 182         SELECT
 183             local
 184         FROM
 185             user_ u
 186         WHERE
 187             u.id = um.recipient_id) AS recipient_local
 188 FROM
 189     user_mention um,
 190     comment_view c
 191 WHERE
 192     um.comment_id = c.id;
 193
 194 CREATE VIEW user_mention_fast_view AS
 195 SELECT
 196     ac.id,
 197     um.id AS user_mention_id,
 198     ac.creator_id,
 199     ac.creator_actor_id,
 200     ac.creator_local,
 201     ac.post_id,
 202     ac.post_name,
 203     ac.parent_id,
 204     ac.content,
 205     ac.removed,
 206     um.read,
 207     ac.published,
 208     ac.updated,
 209     ac.deleted,
 210     ac.community_id,
 211     ac.community_actor_id,
 212     ac.community_local,
 213     ac.community_name,
 214     ac.banned,
 215     ac.banned_from_community,
 216     ac.creator_name,
 217     ac.creator_avatar,
 218     ac.score,
 219     ac.upvotes,
 220     ac.downvotes,
 221     ac.hot_rank,
 222     u.id AS user_id,
 223     coalesce(cl.score, 0) AS my_vote,
 224     (
 225         SELECT
 226             cs.id::bool
 227         FROM
 228             comment_saved cs
 229         WHERE
 230             u.id = cs.user_id
 231             AND cs.comment_id = ac.id) AS saved,
 232     um.recipient_id,
 233     (
 234         SELECT
 235             actor_id
 236         FROM
 237             user_ u
 238         WHERE
 239             u.id = um.recipient_id) AS recipient_actor_id,
 240     (
 241         SELECT
 242             local
 243         FROM
 244             user_ u
 245         WHERE
 246             u.id = um.recipient_id) AS recipient_local
 247 FROM
 248     user_ u
 249     CROSS JOIN (
 250         SELECT
 251             ca.*
 252         FROM
 253             comment_aggregates_fast ca) ac
 254     LEFT JOIN comment_like cl ON u.id = cl.user_id
 255         AND ac.id = cl.comment_id
 256     LEFT JOIN user_mention um ON um.comment_id = ac.id
 257 UNION ALL
 258 SELECT
 259     ac.id,
 260     um.id AS user_mention_id,
 261     ac.creator_id,
 262     ac.creator_actor_id,
 263     ac.creator_local,
 264     ac.post_id,
 265     ac.post_name,
 266     ac.parent_id,
 267     ac.content,
 268     ac.removed,
 269     um.read,
 270     ac.published,
 271     ac.updated,
 272     ac.deleted,
 273     ac.community_id,
 274     ac.community_actor_id,
 275     ac.community_local,
 276     ac.community_name,
 277     ac.banned,
 278     ac.banned_from_community,
 279     ac.creator_name,
 280     ac.creator_avatar,
 281     ac.score,
 282     ac.upvotes,
 283     ac.downvotes,
 284     ac.hot_rank,
 285     NULL AS user_id,
 286     NULL AS my_vote,
 287     NULL AS saved,
 288     um.recipient_id,
 289     (
 290         SELECT
 291             actor_id
 292         FROM
 293             user_ u
 294         WHERE
 295             u.id = um.recipient_id) AS recipient_actor_id,
 296     (
 297         SELECT
 298             local
 299         FROM
 300             user_ u
 301         WHERE
 302             u.id = um.recipient_id) AS recipient_local
 303 FROM
 304     comment_aggregates_fast ac
 305     LEFT JOIN user_mention um ON um.comment_id = ac.id;
 306
 307 -- Do the reply_view referencing the comment_fast_view
 308 CREATE VIEW reply_fast_view AS
 309 with closereply AS (
 310     SELECT
 311         c2.id,
 312         c2.creator_id AS sender_id,
 313         c.creator_id AS recipient_id
 314     FROM
 315         comment c
 316         INNER JOIN comment c2 ON c.id = c2.parent_id
 317     WHERE
 318         c2.creator_id != c.creator_id
 319         -- Do union where post is null
 320     UNION
 321     SELECT
 322         c.id,
 323         c.creator_id AS sender_id,
 324         p.creator_id AS recipient_id
 325     FROM
 326         comment c,
 327         post p
 328     WHERE
 329         c.post_id = p.id
 330         AND c.parent_id IS NULL
 331         AND c.creator_id != p.creator_id
 332 )
 333 SELECT
 334     cv.*,
 335     closereply.recipient_id
 336 FROM
 337     comment_fast_view cv,
 338     closereply
 339 WHERE
 340     closereply.id = cv.id;
 341