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.avatar AS creator_avatar,
31 coalesce(cl.total, 0) AS score,
32 coalesce(cl.up, 0) AS upvotes,
33 coalesce(cl.down, 0) AS downvotes,
34 hot_rank (coalesce(cl.total, 0), ct.published) AS hot_rank
37 LEFT JOIN post p ON ct.post_id = p.id
38 LEFT JOIN community c ON p.community_id = c.id
39 LEFT JOIN user_ u ON ct.creator_id = u.id
40 LEFT JOIN community_user_ban cb ON ct.creator_id = cb.user_id
42 AND p.community_id = cb.community_id
46 sum(l.score) AS total,
48 CASE WHEN l.score = 1 THEN
54 CASE WHEN l.score = - 1 THEN
62 comment_id) AS cl ON cl.id = ct.id;
64 CREATE OR REPLACE VIEW comment_view AS (
67 us.user_id AS user_id,
68 us.my_vote AS my_vote,
69 us.is_subbed::bool AS subscribed,
70 us.is_saved::bool AS saved
72 comment_aggregates_view cav
76 coalesce(cl.score, 0) AS my_vote,
77 coalesce(cf.id, 0) AS is_subbed,
78 coalesce(cs.id, 0) AS is_saved
81 LEFT JOIN comment_like cl ON u.id = cl.user_id
82 AND cav.id = cl.comment_id
83 LEFT JOIN comment_saved cs ON u.id = cs.user_id
84 AND cs.comment_id = cav.id
85 LEFT JOIN community_follower cf ON u.id = cf.user_id
86 AND cav.community_id = cf.community_id) AS us
95 comment_aggregates_view cav);
97 CREATE TABLE comment_aggregates_fast AS
101 comment_aggregates_view;
103 ALTER TABLE comment_aggregates_fast
104 ADD PRIMARY KEY (id);
106 CREATE VIEW comment_fast_view AS
109 us.user_id AS user_id,
110 us.my_vote AS my_vote,
111 us.is_subbed::bool AS subscribed,
112 us.is_saved::bool AS saved
114 comment_aggregates_fast cav
118 coalesce(cl.score, 0) AS my_vote,
119 coalesce(cf.id, 0) AS is_subbed,
120 coalesce(cs.id, 0) AS is_saved
123 LEFT JOIN comment_like cl ON u.id = cl.user_id
124 AND cav.id = cl.comment_id
125 LEFT JOIN comment_saved cs ON u.id = cs.user_id
126 AND cs.comment_id = cav.id
127 LEFT JOIN community_follower cf ON u.id = cf.user_id
128 AND cav.community_id = cf.community_id) AS us
137 comment_aggregates_fast cav;
139 CREATE VIEW user_mention_view AS
142 um.id AS user_mention_id,
155 c.community_actor_id,
159 c.banned_from_community,
176 u.id = um.recipient_id) AS recipient_actor_id,
183 u.id = um.recipient_id) AS recipient_local
188 um.comment_id = c.id;
190 CREATE VIEW user_mention_fast_view AS
193 um.id AS user_mention_id,
206 ac.community_actor_id,
210 ac.banned_from_community,
218 coalesce(cl.score, 0) AS my_vote,
226 AND cs.comment_id = ac.id) AS saved,
234 u.id = um.recipient_id) AS recipient_actor_id,
241 u.id = um.recipient_id) AS recipient_local
248 comment_aggregates_fast ca) ac
249 LEFT JOIN comment_like cl ON u.id = cl.user_id
250 AND ac.id = cl.comment_id
251 LEFT JOIN user_mention um ON um.comment_id = ac.id
255 um.id AS user_mention_id,
268 ac.community_actor_id,
272 ac.banned_from_community,
289 u.id = um.recipient_id) AS recipient_actor_id,
296 u.id = um.recipient_id) AS recipient_local
298 comment_aggregates_fast ac
299 LEFT JOIN user_mention um ON um.comment_id = ac.id;
301 -- Do the reply_view referencing the comment_fast_view
302 CREATE VIEW reply_fast_view AS
306 c2.creator_id AS sender_id,
307 c.creator_id AS recipient_id
310 INNER JOIN comment c2 ON c.id = c2.parent_id
312 c2.creator_id != c.creator_id
313 -- Do union where post is null
317 c.creator_id AS sender_id,
318 p.creator_id AS recipient_id
324 AND c.parent_id IS NULL
325 AND c.creator_id != p.creator_id
329 closereply.recipient_id
331 comment_fast_view cv,
334 closereply.id = cv.id;
336 -- add creator_published to the post view
337 DROP VIEW post_fast_view;
339 DROP TABLE post_aggregates_fast;
343 DROP VIEW post_aggregates_view;
345 CREATE VIEW post_aggregates_view AS
349 u.actor_id AS creator_actor_id,
350 u."local" AS creator_local,
351 u."name" AS creator_name,
352 u.avatar AS creator_avatar,
354 cb.id::bool AS banned_from_community,
356 c.actor_id AS community_actor_id,
357 c."local" AS community_local,
358 c."name" AS community_name,
359 c.removed AS community_removed,
360 c.deleted AS community_deleted,
361 c.nsfw AS community_nsfw,
362 -- post score data/comment count
363 coalesce(ct.comments, 0) AS number_of_comments,
364 coalesce(pl.score, 0) AS score,
365 coalesce(pl.upvotes, 0) AS upvotes,
366 coalesce(pl.downvotes, 0) AS downvotes,
367 hot_rank (coalesce(pl.score, 0), (
368 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
371 greatest (ct.recent_comment_time, p.published)
374 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
377 greatest (ct.recent_comment_time, p.published)
378 END) AS newest_activity_time
381 LEFT JOIN user_ u ON p.creator_id = u.id
382 LEFT JOIN community_user_ban cb ON p.creator_id = cb.user_id
383 AND p.community_id = cb.community_id
384 LEFT JOIN community c ON p.community_id = c.id
388 count(*) AS comments,
389 max(published) AS recent_comment_time
393 post_id) ct ON ct.post_id = p.id
398 sum(score) FILTER (WHERE score = 1) AS upvotes,
399 - sum(score) FILTER (WHERE score = - 1) AS downvotes
403 post_id) pl ON pl.post_id = p.id
407 CREATE VIEW post_view AS
411 us.user_vote AS my_vote,
412 us.is_subbed::bool AS subscribed,
413 us.is_read::bool AS read,
414 us.is_saved::bool AS saved
416 post_aggregates_view pav
420 coalesce(cf.community_id, 0) AS is_subbed,
421 coalesce(pr.post_id, 0) AS is_read,
422 coalesce(ps.post_id, 0) AS is_saved,
423 coalesce(pl.score, 0) AS user_vote
426 LEFT JOIN community_user_ban cb ON u.id = cb.user_id
427 AND cb.community_id = pav.community_id
428 LEFT JOIN community_follower cf ON u.id = cf.user_id
429 AND cf.community_id = pav.community_id
430 LEFT JOIN post_read pr ON u.id = pr.user_id
431 AND pr.post_id = pav.id
432 LEFT JOIN post_saved ps ON u.id = ps.user_id
433 AND ps.post_id = pav.id
434 LEFT JOIN post_like pl ON u.id = pl.user_id
435 AND pav.id = pl.post_id) AS us
445 post_aggregates_view pav;
447 CREATE TABLE post_aggregates_fast AS
451 post_aggregates_view;
453 ALTER TABLE post_aggregates_fast
454 ADD PRIMARY KEY (id);
456 CREATE VIEW post_fast_view AS
460 us.user_vote AS my_vote,
461 us.is_subbed::bool AS subscribed,
462 us.is_read::bool AS read,
463 us.is_saved::bool AS saved
465 post_aggregates_fast pav
469 coalesce(cf.community_id, 0) AS is_subbed,
470 coalesce(pr.post_id, 0) AS is_read,
471 coalesce(ps.post_id, 0) AS is_saved,
472 coalesce(pl.score, 0) AS user_vote
475 LEFT JOIN community_user_ban cb ON u.id = cb.user_id
476 AND cb.community_id = pav.community_id
477 LEFT JOIN community_follower cf ON u.id = cf.user_id
478 AND cf.community_id = pav.community_id
479 LEFT JOIN post_read pr ON u.id = pr.user_id
480 AND pr.post_id = pav.id
481 LEFT JOIN post_saved ps ON u.id = ps.user_id
482 AND ps.post_id = pav.id
483 LEFT JOIN post_like pl ON u.id = pl.user_id
484 AND pav.id = pl.post_id) AS us
494 post_aggregates_fast pav;