1 DROP VIEW user_mention_view;
3 DROP VIEW reply_fast_view;
5 DROP VIEW comment_fast_view;
7 DROP VIEW comment_view;
9 DROP VIEW user_mention_fast_view;
11 DROP TABLE comment_aggregates_fast;
13 DROP VIEW comment_aggregates_view;
15 CREATE VIEW comment_aggregates_view AS
20 c.actor_id AS community_actor_id,
21 c."local" AS community_local,
22 c."name" AS community_name,
25 coalesce(cb.id, 0)::bool AS banned_from_community,
26 u.actor_id AS creator_actor_id,
27 u.local AS creator_local,
28 u.name AS creator_name,
29 u.published AS creator_published,
30 u.avatar AS creator_avatar,
32 coalesce(cl.total, 0) AS score,
33 coalesce(cl.up, 0) AS upvotes,
34 coalesce(cl.down, 0) AS downvotes,
35 hot_rank (coalesce(cl.total, 0), ct.published) AS hot_rank
38 LEFT JOIN post p ON ct.post_id = p.id
39 LEFT JOIN community c ON p.community_id = c.id
40 LEFT JOIN user_ u ON ct.creator_id = u.id
41 LEFT JOIN community_user_ban cb ON ct.creator_id = cb.user_id
43 AND p.community_id = cb.community_id
47 sum(l.score) AS total,
49 CASE WHEN l.score = 1 THEN
55 CASE WHEN l.score = - 1 THEN
63 comment_id) AS cl ON cl.id = ct.id;
65 CREATE OR REPLACE VIEW comment_view AS (
68 us.user_id AS user_id,
69 us.my_vote AS my_vote,
70 us.is_subbed::bool AS subscribed,
71 us.is_saved::bool AS saved
73 comment_aggregates_view cav
77 coalesce(cl.score, 0) AS my_vote,
78 coalesce(cf.id, 0) AS is_subbed,
79 coalesce(cs.id, 0) AS is_saved
82 LEFT JOIN comment_like cl ON u.id = cl.user_id
83 AND cav.id = cl.comment_id
84 LEFT JOIN comment_saved cs ON u.id = cs.user_id
85 AND cs.comment_id = cav.id
86 LEFT JOIN community_follower cf ON u.id = cf.user_id
87 AND cav.community_id = cf.community_id) AS us
96 comment_aggregates_view cav);
98 CREATE TABLE comment_aggregates_fast AS
102 comment_aggregates_view;
104 ALTER TABLE comment_aggregates_fast
105 ADD PRIMARY KEY (id);
107 CREATE VIEW comment_fast_view AS
110 us.user_id AS user_id,
111 us.my_vote AS my_vote,
112 us.is_subbed::bool AS subscribed,
113 us.is_saved::bool AS saved
115 comment_aggregates_fast cav
119 coalesce(cl.score, 0) AS my_vote,
120 coalesce(cf.id, 0) AS is_subbed,
121 coalesce(cs.id, 0) AS is_saved
124 LEFT JOIN comment_like cl ON u.id = cl.user_id
125 AND cav.id = cl.comment_id
126 LEFT JOIN comment_saved cs ON u.id = cs.user_id
127 AND cs.comment_id = cav.id
128 LEFT JOIN community_follower cf ON u.id = cf.user_id
129 AND cav.community_id = cf.community_id) AS us
138 comment_aggregates_fast cav;
140 CREATE VIEW user_mention_view AS
143 um.id AS user_mention_id,
156 c.community_actor_id,
160 c.banned_from_community,
177 u.id = um.recipient_id) AS recipient_actor_id,
184 u.id = um.recipient_id) AS recipient_local
189 um.comment_id = c.id;
191 CREATE VIEW user_mention_fast_view AS
194 um.id AS user_mention_id,
207 ac.community_actor_id,
211 ac.banned_from_community,
219 coalesce(cl.score, 0) AS my_vote,
227 AND cs.comment_id = ac.id) AS saved,
235 u.id = um.recipient_id) AS recipient_actor_id,
242 u.id = um.recipient_id) AS recipient_local
249 comment_aggregates_fast ca) ac
250 LEFT JOIN comment_like cl ON u.id = cl.user_id
251 AND ac.id = cl.comment_id
252 LEFT JOIN user_mention um ON um.comment_id = ac.id
256 um.id AS user_mention_id,
269 ac.community_actor_id,
273 ac.banned_from_community,
290 u.id = um.recipient_id) AS recipient_actor_id,
297 u.id = um.recipient_id) AS recipient_local
299 comment_aggregates_fast ac
300 LEFT JOIN user_mention um ON um.comment_id = ac.id;
302 -- Do the reply_view referencing the comment_fast_view
303 CREATE VIEW reply_fast_view AS
307 c2.creator_id AS sender_id,
308 c.creator_id AS recipient_id
311 INNER JOIN comment c2 ON c.id = c2.parent_id
313 c2.creator_id != c.creator_id
314 -- Do union where post is null
318 c.creator_id AS sender_id,
319 p.creator_id AS recipient_id
325 AND c.parent_id IS NULL
326 AND c.creator_id != p.creator_id
330 closereply.recipient_id
332 comment_fast_view cv,
335 closereply.id = cv.id;
337 -- add creator_published to the post view
338 DROP VIEW post_fast_view;
340 DROP TABLE post_aggregates_fast;
344 DROP VIEW post_aggregates_view;
346 CREATE VIEW post_aggregates_view AS
350 u.actor_id AS creator_actor_id,
351 u."local" AS creator_local,
352 u."name" AS creator_name,
353 u.published AS creator_published,
354 u.avatar AS creator_avatar,
356 cb.id::bool AS banned_from_community,
358 c.actor_id AS community_actor_id,
359 c."local" AS community_local,
360 c."name" AS community_name,
361 c.removed AS community_removed,
362 c.deleted AS community_deleted,
363 c.nsfw AS community_nsfw,
364 -- post score data/comment count
365 coalesce(ct.comments, 0) AS number_of_comments,
366 coalesce(pl.score, 0) AS score,
367 coalesce(pl.upvotes, 0) AS upvotes,
368 coalesce(pl.downvotes, 0) AS downvotes,
369 hot_rank (coalesce(pl.score, 0), (
370 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
373 greatest (ct.recent_comment_time, p.published)
376 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
379 greatest (ct.recent_comment_time, p.published)
380 END) AS newest_activity_time
383 LEFT JOIN user_ u ON p.creator_id = u.id
384 LEFT JOIN community_user_ban cb ON p.creator_id = cb.user_id
385 AND p.community_id = cb.community_id
386 LEFT JOIN community c ON p.community_id = c.id
390 count(*) AS comments,
391 max(published) AS recent_comment_time
395 post_id) ct ON ct.post_id = p.id
400 sum(score) FILTER (WHERE score = 1) AS upvotes,
401 - sum(score) FILTER (WHERE score = - 1) AS downvotes
405 post_id) pl ON pl.post_id = p.id
409 CREATE VIEW post_view AS
413 us.user_vote AS my_vote,
414 us.is_subbed::bool AS subscribed,
415 us.is_read::bool AS read,
416 us.is_saved::bool AS saved
418 post_aggregates_view pav
422 coalesce(cf.community_id, 0) AS is_subbed,
423 coalesce(pr.post_id, 0) AS is_read,
424 coalesce(ps.post_id, 0) AS is_saved,
425 coalesce(pl.score, 0) AS user_vote
428 LEFT JOIN community_user_ban cb ON u.id = cb.user_id
429 AND cb.community_id = pav.community_id
430 LEFT JOIN community_follower cf ON u.id = cf.user_id
431 AND cf.community_id = pav.community_id
432 LEFT JOIN post_read pr ON u.id = pr.user_id
433 AND pr.post_id = pav.id
434 LEFT JOIN post_saved ps ON u.id = ps.user_id
435 AND ps.post_id = pav.id
436 LEFT JOIN post_like pl ON u.id = pl.user_id
437 AND pav.id = pl.post_id) AS us
447 post_aggregates_view pav;
449 CREATE TABLE post_aggregates_fast AS
453 post_aggregates_view;
455 ALTER TABLE post_aggregates_fast
456 ADD PRIMARY KEY (id);
458 CREATE VIEW post_fast_view AS
462 us.user_vote AS my_vote,
463 us.is_subbed::bool AS subscribed,
464 us.is_read::bool AS read,
465 us.is_saved::bool AS saved
467 post_aggregates_fast pav
471 coalesce(cf.community_id, 0) AS is_subbed,
472 coalesce(pr.post_id, 0) AS is_read,
473 coalesce(ps.post_id, 0) AS is_saved,
474 coalesce(pl.score, 0) AS user_vote
477 LEFT JOIN community_user_ban cb ON u.id = cb.user_id
478 AND cb.community_id = pav.community_id
479 LEFT JOIN community_follower cf ON u.id = cf.user_id
480 AND cf.community_id = pav.community_id
481 LEFT JOIN post_read pr ON u.id = pr.user_id
482 AND pr.post_id = pav.id
483 LEFT JOIN post_saved ps ON u.id = ps.user_id
484 AND ps.post_id = pav.id
485 LEFT JOIN post_like pl ON u.id = pl.user_id
486 AND pav.id = pl.post_id) AS us
496 post_aggregates_fast pav;