2 DROP VIEW user_view CASCADE;
4 CREATE VIEW user_view AS
14 u.send_notifications_to_email,
22 p.creator_id = u.id) AS number_of_posts,
25 coalesce(sum(score), 0)
31 AND p.id = pl.post_id) AS post_score,
38 c.creator_id = u.id) AS number_of_comments,
41 coalesce(sum(score), 0)
47 AND c.id = cl.comment_id) AS comment_score
51 CREATE MATERIALIZED VIEW user_mview AS
57 CREATE UNIQUE INDEX idx_user_mview_id ON user_mview (id);
60 DROP VIEW community_aggregates_view CASCADE;
62 CREATE VIEW community_aggregates_view AS
71 c.creator_id = u.id) AS creator_name,
78 c.creator_id = u.id) AS creator_avatar,
85 c.category_id = ct.id) AS category_name,
92 cf.community_id = c.id) AS number_of_subscribers,
99 p.community_id = c.id) AS number_of_posts,
107 c.id = p.community_id
108 AND p.id = co.post_id) AS number_of_comments,
112 FROM community_follower cf
114 cf.community_id = c.id), c.published) AS hot_rank
118 CREATE MATERIALIZED VIEW community_aggregates_mview AS
122 community_aggregates_view;
124 CREATE UNIQUE INDEX idx_community_aggregates_mview_id ON community_aggregates_mview (id);
126 CREATE VIEW community_view AS
127 with all_community AS (
131 community_aggregates_view ca
140 community_follower cf
143 AND ac.id = cf.community_id) AS subscribed
146 CROSS JOIN all_community ac
155 CREATE VIEW community_mview AS
156 with all_community AS (
160 community_aggregates_mview ca
169 community_follower cf
172 AND ac.id = cf.community_id) AS subscribed
175 CROSS JOIN all_community ac
185 DROP VIEW community_moderator_view;
187 DROP VIEW community_follower_view;
189 DROP VIEW community_user_ban_view;
191 CREATE VIEW community_moderator_view AS
200 cm.user_id = u.id) AS user_name,
207 cm.user_id = u.id), (
213 cm.community_id = c.id) AS community_name
215 community_moderator cm;
217 CREATE VIEW community_follower_view AS
226 cf.user_id = u.id) AS user_name,
233 cf.user_id = u.id), (
239 cf.community_id = c.id) AS community_name
241 community_follower cf;
243 CREATE VIEW community_user_ban_view AS
252 cm.user_id = u.id) AS user_name,
259 cm.user_id = u.id), (
265 cm.community_id = c.id) AS community_name
267 community_user_ban cm;
272 DROP VIEW post_mview;
274 DROP MATERIALIZED VIEW post_aggregates_mview;
276 DROP VIEW post_aggregates_view;
279 CREATE VIEW post_aggregates_view AS
288 p.creator_id = u.id) AS banned,
293 community_user_ban cb
295 p.creator_id = cb.user_id
296 AND p.community_id = cb.community_id) AS banned_from_community,
303 p.creator_id = user_.id) AS creator_name,
310 p.creator_id = user_.id) AS creator_avatar,
317 p.community_id = community.id) AS community_name,
324 p.community_id = c.id) AS community_removed,
331 p.community_id = c.id) AS community_deleted,
338 p.community_id = c.id) AS community_nsfw,
345 comment.post_id = p.id) AS number_of_comments,
346 coalesce(sum(pl.score), 0) AS score,
348 CASE WHEN pl.score = 1 THEN
354 CASE WHEN pl.score = - 1 THEN
359 hot_rank (coalesce(sum(pl.score), 0), (
360 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
361 p.published -- Prevents necro-bumps
363 greatest (c.recent_comment_time, p.published)
366 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
367 p.published -- Prevents necro-bumps
369 greatest (c.recent_comment_time, p.published)
370 END) AS newest_activity_time
373 LEFT JOIN post_like pl ON p.id = pl.post_id
377 max(published) AS recent_comment_time
381 1) c ON p.id = c.post_id
384 c.recent_comment_time;
386 CREATE MATERIALIZED VIEW post_aggregates_mview AS
390 post_aggregates_view;
392 CREATE UNIQUE INDEX idx_post_aggregates_mview_id ON post_aggregates_mview (id);
394 CREATE VIEW post_view AS
399 post_aggregates_view pa
404 coalesce(pl.score, 0) AS my_vote,
409 community_follower cf
412 AND cf.community_id = ap.community_id) AS subscribed,
420 AND pr.post_id = ap.id) AS read,
428 AND ps.post_id = ap.id) AS saved
431 CROSS JOIN all_post ap
432 LEFT JOIN post_like pl ON u.id = pl.user_id
433 AND ap.id = pl.post_id
445 CREATE VIEW post_mview AS
450 post_aggregates_mview pa
455 coalesce(pl.score, 0) AS my_vote,
460 community_follower cf
463 AND cf.community_id = ap.community_id) AS subscribed,
471 AND pr.post_id = ap.id) AS read,
479 AND ps.post_id = ap.id) AS saved
482 CROSS JOIN all_post ap
483 LEFT JOIN post_like pl ON u.id = pl.user_id
484 AND ap.id = pl.post_id
496 -- reply_view, comment_view, user_mention
497 DROP VIEW reply_view;
499 DROP VIEW user_mention_view;
501 DROP VIEW user_mention_mview;
503 DROP VIEW comment_view;
505 DROP VIEW comment_mview;
507 DROP MATERIALIZED VIEW comment_aggregates_mview;
509 DROP VIEW comment_aggregates_view;
511 -- reply and comment view
512 CREATE VIEW comment_aggregates_view AS
529 AND p.community_id = co.id) AS community_name,
536 c.creator_id = u.id) AS banned,
541 community_user_ban cb,
544 c.creator_id = cb.user_id
546 AND p.community_id = cb.community_id) AS banned_from_community,
553 c.creator_id = user_.id) AS creator_name,
560 c.creator_id = user_.id) AS creator_avatar,
561 coalesce(sum(cl.score), 0) AS score,
563 CASE WHEN cl.score = 1 THEN
569 CASE WHEN cl.score = - 1 THEN
574 hot_rank (coalesce(sum(cl.score), 0), c.published) AS hot_rank
577 LEFT JOIN comment_like cl ON c.id = cl.comment_id
581 CREATE MATERIALIZED VIEW comment_aggregates_mview AS
585 comment_aggregates_view;
587 CREATE UNIQUE INDEX idx_comment_aggregates_mview_id ON comment_aggregates_mview (id);
589 CREATE VIEW comment_view AS
590 with all_comment AS (
594 comment_aggregates_view ca
599 coalesce(cl.score, 0) AS my_vote,
604 community_follower cf
607 AND ac.community_id = cf.community_id) AS subscribed,
615 AND cs.comment_id = ac.id) AS saved
618 CROSS JOIN all_comment ac
619 LEFT JOIN comment_like cl ON u.id = cl.user_id
620 AND ac.id = cl.comment_id
631 CREATE VIEW comment_mview AS
632 with all_comment AS (
636 comment_aggregates_mview ca
641 coalesce(cl.score, 0) AS my_vote,
646 community_follower cf
649 AND ac.community_id = cf.community_id) AS subscribed,
657 AND cs.comment_id = ac.id) AS saved
660 CROSS JOIN all_comment ac
661 LEFT JOIN comment_like cl ON u.id = cl.user_id
662 AND ac.id = cl.comment_id
673 -- Do the reply_view referencing the comment_mview
674 CREATE VIEW reply_view AS
678 c2.creator_id AS sender_id,
679 c.creator_id AS recipient_id
682 INNER JOIN comment c2 ON c.id = c2.parent_id
684 c2.creator_id != c.creator_id
685 -- Do union where post is null
689 c.creator_id AS sender_id,
690 p.creator_id AS recipient_id
696 AND c.parent_id IS NULL
697 AND c.creator_id != p.creator_id
701 closereply.recipient_id
706 closereply.id = cv.id;
709 CREATE VIEW user_mention_view AS
712 um.id AS user_mention_id,
725 c.banned_from_community,
740 um.comment_id = c.id;
742 CREATE VIEW user_mention_mview AS
743 with all_comment AS (
747 comment_aggregates_mview ca
751 um.id AS user_mention_id,
764 ac.banned_from_community,
772 coalesce(cl.score, 0) AS my_vote,
780 AND cs.comment_id = ac.id) AS saved,
784 CROSS JOIN all_comment ac
785 LEFT JOIN comment_like cl ON u.id = cl.user_id
786 AND ac.id = cl.comment_id
787 LEFT JOIN user_mention um ON um.comment_id = ac.id
791 um.id AS user_mention_id,
804 ac.banned_from_community,
817 LEFT JOIN user_mention um ON um.comment_id = ac.id;