2 CREATE VIEW post_aggregates_view AS
11 p.creator_id = u.id) AS banned,
18 p.creator_id = cb.user_id
19 AND p.community_id = cb.community_id) AS banned_from_community,
26 p.creator_id = user_.id) AS creator_name,
33 p.creator_id = user_.id) AS creator_avatar,
40 p.community_id = community.id) AS community_name,
47 p.community_id = c.id) AS community_removed,
54 p.community_id = c.id) AS community_deleted,
61 p.community_id = c.id) AS community_nsfw,
68 comment.post_id = p.id) AS number_of_comments,
69 coalesce(sum(pl.score), 0) AS score,
71 CASE WHEN pl.score = 1 THEN
77 CASE WHEN pl.score = - 1 THEN
82 hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
85 LEFT JOIN post_like pl ON p.id = pl.post_id
89 CREATE MATERIALIZED VIEW post_aggregates_mview AS
95 CREATE UNIQUE INDEX idx_post_aggregates_mview_id ON post_aggregates_mview (id);
99 CREATE VIEW post_view AS
104 post_aggregates_view pa
109 coalesce(pl.score, 0) AS my_vote,
114 community_follower cf
117 AND cf.community_id = ap.community_id) AS subscribed,
125 AND pr.post_id = ap.id) AS read,
133 AND ps.post_id = ap.id) AS saved
136 CROSS JOIN all_post ap
137 LEFT JOIN post_like pl ON u.id = pl.user_id
138 AND ap.id = pl.post_id
150 CREATE VIEW post_mview AS
155 post_aggregates_mview pa
160 coalesce(pl.score, 0) AS my_vote,
165 community_follower cf
168 AND cf.community_id = ap.community_id) AS subscribed,
176 AND pr.post_id = ap.id) AS read,
184 AND ps.post_id = ap.id) AS saved
187 CROSS JOIN all_post ap
188 LEFT JOIN post_like pl ON u.id = pl.user_id
189 AND ap.id = pl.post_id
204 CREATE VIEW user_view AS
214 u.send_notifications_to_email,
222 p.creator_id = u.id) AS number_of_posts,
225 coalesce(sum(score), 0)
231 AND p.id = pl.post_id) AS post_score,
238 c.creator_id = u.id) AS number_of_comments,
241 coalesce(sum(score), 0)
247 AND c.id = cl.comment_id) AS comment_score
251 CREATE MATERIALIZED VIEW user_mview AS
257 CREATE UNIQUE INDEX idx_user_mview_id ON user_mview (id);
260 CREATE VIEW community_aggregates_view AS
269 c.creator_id = u.id) AS creator_name,
276 c.creator_id = u.id) AS creator_avatar,
283 c.category_id = ct.id) AS category_name,
288 community_follower cf
290 cf.community_id = c.id) AS number_of_subscribers,
297 p.community_id = c.id) AS number_of_posts,
305 c.id = p.community_id
306 AND p.id = co.post_id) AS number_of_comments,
310 FROM community_follower cf
312 cf.community_id = c.id), c.published) AS hot_rank
316 CREATE MATERIALIZED VIEW community_aggregates_mview AS
320 community_aggregates_view;
322 CREATE UNIQUE INDEX idx_community_aggregates_mview_id ON community_aggregates_mview (id);
324 DROP VIEW community_view;
326 CREATE VIEW community_view AS
327 with all_community AS (
331 community_aggregates_view ca
340 community_follower cf
343 AND ac.id = cf.community_id) AS subscribed
346 CROSS JOIN all_community ac
355 CREATE VIEW community_mview AS
356 with all_community AS (
360 community_aggregates_mview ca
369 community_follower cf
372 AND ac.id = cf.community_id) AS subscribed
375 CROSS JOIN all_community ac
384 -- reply and comment view
385 CREATE VIEW comment_aggregates_view AS
400 c.creator_id = u.id) AS banned,
405 community_user_ban cb,
408 c.creator_id = cb.user_id
410 AND p.community_id = cb.community_id) AS banned_from_community,
417 c.creator_id = user_.id) AS creator_name,
424 c.creator_id = user_.id) AS creator_avatar,
425 coalesce(sum(cl.score), 0) AS score,
427 CASE WHEN cl.score = 1 THEN
433 CASE WHEN cl.score = - 1 THEN
440 LEFT JOIN comment_like cl ON c.id = cl.comment_id
444 CREATE MATERIALIZED VIEW comment_aggregates_mview AS
448 comment_aggregates_view;
450 CREATE UNIQUE INDEX idx_comment_aggregates_mview_id ON comment_aggregates_mview (id);
452 DROP VIEW reply_view;
454 DROP VIEW user_mention_view;
456 DROP VIEW comment_view;
458 CREATE VIEW comment_view AS
459 with all_comment AS (
463 comment_aggregates_view ca
468 coalesce(cl.score, 0) AS my_vote,
476 AND cs.comment_id = ac.id) AS saved
479 CROSS JOIN all_comment ac
480 LEFT JOIN comment_like cl ON u.id = cl.user_id
481 AND ac.id = cl.comment_id
491 CREATE VIEW comment_mview AS
492 with all_comment AS (
496 comment_aggregates_mview ca
501 coalesce(cl.score, 0) AS my_vote,
509 AND cs.comment_id = ac.id) AS saved
512 CROSS JOIN all_comment ac
513 LEFT JOIN comment_like cl ON u.id = cl.user_id
514 AND ac.id = cl.comment_id
524 CREATE VIEW reply_view AS
528 c2.creator_id AS sender_id,
529 c.creator_id AS recipient_id
532 INNER JOIN comment c2 ON c.id = c2.parent_id
534 c2.creator_id != c.creator_id
535 -- Do union where post is null
539 c.creator_id AS sender_id,
540 p.creator_id AS recipient_id
546 AND c.parent_id IS NULL
547 AND c.creator_id != p.creator_id
551 closereply.recipient_id
556 closereply.id = cv.id;
559 CREATE VIEW user_mention_view AS
562 um.id AS user_mention_id,
574 c.banned_from_community,
588 um.comment_id = c.id;
591 CREATE OR REPLACE FUNCTION refresh_user ()
596 REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
597 REFRESH MATERIALIZED VIEW CONCURRENTLY comment_aggregates_mview;
599 REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
604 CREATE TRIGGER refresh_user
605 AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON user_
607 EXECUTE PROCEDURE refresh_user ();
610 CREATE OR REPLACE FUNCTION refresh_post ()
615 REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
616 REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
621 CREATE TRIGGER refresh_post
622 AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON post
624 EXECUTE PROCEDURE refresh_post ();
627 CREATE OR REPLACE FUNCTION refresh_post_like ()
632 REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
633 REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
638 CREATE TRIGGER refresh_post_like
639 AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON post_like
641 EXECUTE PROCEDURE refresh_post_like ();
644 CREATE OR REPLACE FUNCTION refresh_community ()
649 REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
650 REFRESH MATERIALIZED VIEW CONCURRENTLY community_aggregates_mview;
651 REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
656 CREATE TRIGGER refresh_community
657 AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON community
659 EXECUTE PROCEDURE refresh_community ();
661 -- community_follower
662 CREATE OR REPLACE FUNCTION refresh_community_follower ()
667 REFRESH MATERIALIZED VIEW CONCURRENTLY community_aggregates_mview;
668 REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
673 CREATE TRIGGER refresh_community_follower
674 AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON community_follower
676 EXECUTE PROCEDURE refresh_community_follower ();
678 -- community_user_ban
679 CREATE OR REPLACE FUNCTION refresh_community_user_ban ()
684 REFRESH MATERIALIZED VIEW CONCURRENTLY comment_aggregates_mview;
685 REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
690 CREATE TRIGGER refresh_community_user_ban
691 AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON community_user_ban
693 EXECUTE PROCEDURE refresh_community_user_ban ();
696 CREATE OR REPLACE FUNCTION refresh_comment ()
701 REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
702 REFRESH MATERIALIZED VIEW CONCURRENTLY comment_aggregates_mview;
703 REFRESH MATERIALIZED VIEW CONCURRENTLY community_aggregates_mview;
704 REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
709 CREATE TRIGGER refresh_comment
710 AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON comment
712 EXECUTE PROCEDURE refresh_comment ();
715 CREATE OR REPLACE FUNCTION refresh_comment_like ()
720 REFRESH MATERIALIZED VIEW CONCURRENTLY comment_aggregates_mview;
721 REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
726 CREATE TRIGGER refresh_comment_like
727 AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON comment_like
729 EXECUTE PROCEDURE refresh_comment_like ();