2 DROP VIEW user_view CASCADE;
4 CREATE VIEW user_view AS
17 u.send_notifications_to_email,
25 p.creator_id = u.id) AS number_of_posts,
28 coalesce(sum(score), 0)
34 AND p.id = pl.post_id) AS post_score,
41 c.creator_id = u.id) AS number_of_comments,
44 coalesce(sum(score), 0)
50 AND c.id = cl.comment_id) AS comment_score
54 CREATE MATERIALIZED VIEW user_mview AS
60 CREATE UNIQUE INDEX idx_user_mview_id ON user_mview (id);
63 DROP VIEW community_aggregates_view CASCADE;
65 CREATE VIEW community_aggregates_view AS
66 -- Now that there's public and private keys, you have to be explicit here
88 c.creator_id = u.id) AS creator_actor_id,
95 c.creator_id = u.id) AS creator_local,
102 c.creator_id = u.id) AS creator_name,
109 c.creator_id = u.id) AS creator_avatar,
116 c.category_id = ct.id) AS category_name,
121 community_follower cf
123 cf.community_id = c.id) AS number_of_subscribers,
130 p.community_id = c.id) AS number_of_posts,
138 c.id = p.community_id
139 AND p.id = co.post_id) AS number_of_comments,
143 FROM community_follower cf
145 cf.community_id = c.id), c.published) AS hot_rank
149 CREATE MATERIALIZED VIEW community_aggregates_mview AS
153 community_aggregates_view;
155 CREATE UNIQUE INDEX idx_community_aggregates_mview_id ON community_aggregates_mview (id);
157 CREATE VIEW community_view AS
158 with all_community AS (
162 community_aggregates_view ca
171 community_follower cf
174 AND ac.id = cf.community_id) AS subscribed
177 CROSS JOIN all_community ac
186 CREATE VIEW community_mview AS
187 with all_community AS (
191 community_aggregates_mview ca
200 community_follower cf
203 AND ac.id = cf.community_id) AS subscribed
206 CROSS JOIN all_community ac
216 DROP VIEW community_moderator_view;
218 DROP VIEW community_follower_view;
220 DROP VIEW community_user_ban_view;
222 CREATE VIEW community_moderator_view AS
231 cm.user_id = u.id) AS user_actor_id,
238 cm.user_id = u.id) AS user_local,
245 cm.user_id = u.id) AS user_name,
252 cm.user_id = u.id), (
258 cm.community_id = c.id) AS community_actor_id,
265 cm.community_id = c.id) AS community_local,
272 cm.community_id = c.id) AS community_name
274 community_moderator cm;
276 CREATE VIEW community_follower_view AS
285 cf.user_id = u.id) AS user_actor_id,
292 cf.user_id = u.id) AS user_local,
299 cf.user_id = u.id) AS user_name,
306 cf.user_id = u.id), (
312 cf.community_id = c.id) AS community_actor_id,
319 cf.community_id = c.id) AS community_local,
326 cf.community_id = c.id) AS community_name
328 community_follower cf;
330 CREATE VIEW community_user_ban_view AS
339 cm.user_id = u.id) AS user_actor_id,
346 cm.user_id = u.id) AS user_local,
353 cm.user_id = u.id) AS user_name,
360 cm.user_id = u.id), (
366 cm.community_id = c.id) AS community_actor_id,
373 cm.community_id = c.id) AS community_local,
380 cm.community_id = c.id) AS community_name
382 community_user_ban cm;
387 DROP VIEW post_mview;
389 DROP MATERIALIZED VIEW post_aggregates_mview;
391 DROP VIEW post_aggregates_view;
394 CREATE VIEW post_aggregates_view AS
403 p.creator_id = u.id) AS banned,
408 community_user_ban cb
410 p.creator_id = cb.user_id
411 AND p.community_id = cb.community_id) AS banned_from_community,
418 p.creator_id = user_.id) AS creator_actor_id,
425 p.creator_id = user_.id) AS creator_local,
432 p.creator_id = user_.id) AS creator_name,
439 p.creator_id = user_.id) AS creator_avatar,
446 p.community_id = community.id) AS community_actor_id,
453 p.community_id = community.id) AS community_local,
460 p.community_id = community.id) AS community_name,
467 p.community_id = c.id) AS community_removed,
474 p.community_id = c.id) AS community_deleted,
481 p.community_id = c.id) AS community_nsfw,
488 comment.post_id = p.id) AS number_of_comments,
489 coalesce(sum(pl.score), 0) AS score,
491 CASE WHEN pl.score = 1 THEN
497 CASE WHEN pl.score = - 1 THEN
502 hot_rank (coalesce(sum(pl.score), 0), (
503 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
504 p.published -- Prevents necro-bumps
506 greatest (c.recent_comment_time, p.published)
509 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
510 p.published -- Prevents necro-bumps
512 greatest (c.recent_comment_time, p.published)
513 END) AS newest_activity_time
516 LEFT JOIN post_like pl ON p.id = pl.post_id
520 max(published) AS recent_comment_time
524 1) c ON p.id = c.post_id
527 c.recent_comment_time;
529 CREATE MATERIALIZED VIEW post_aggregates_mview AS
533 post_aggregates_view;
535 CREATE UNIQUE INDEX idx_post_aggregates_mview_id ON post_aggregates_mview (id);
537 CREATE VIEW post_view AS
542 post_aggregates_view pa
547 coalesce(pl.score, 0) AS my_vote,
552 community_follower cf
555 AND cf.community_id = ap.community_id) AS subscribed,
563 AND pr.post_id = ap.id) AS read,
571 AND ps.post_id = ap.id) AS saved
574 CROSS JOIN all_post ap
575 LEFT JOIN post_like pl ON u.id = pl.user_id
576 AND ap.id = pl.post_id
588 CREATE VIEW post_mview AS
593 post_aggregates_mview pa
598 coalesce(pl.score, 0) AS my_vote,
603 community_follower cf
606 AND cf.community_id = ap.community_id) AS subscribed,
614 AND pr.post_id = ap.id) AS read,
622 AND ps.post_id = ap.id) AS saved
625 CROSS JOIN all_post ap
626 LEFT JOIN post_like pl ON u.id = pl.user_id
627 AND ap.id = pl.post_id
639 -- reply_view, comment_view, user_mention
640 DROP VIEW reply_view;
642 DROP VIEW user_mention_view;
644 DROP VIEW user_mention_mview;
646 DROP VIEW comment_view;
648 DROP VIEW comment_mview;
650 DROP MATERIALIZED VIEW comment_aggregates_mview;
652 DROP VIEW comment_aggregates_view;
654 -- reply and comment view
655 CREATE VIEW comment_aggregates_view AS
672 AND p.community_id = co.id) AS community_actor_id,
681 AND p.community_id = co.id) AS community_local,
690 AND p.community_id = co.id) AS community_name,
697 c.creator_id = u.id) AS banned,
702 community_user_ban cb,
705 c.creator_id = cb.user_id
707 AND p.community_id = cb.community_id) AS banned_from_community,
714 c.creator_id = user_.id) AS creator_actor_id,
721 c.creator_id = user_.id) AS creator_local,
728 c.creator_id = user_.id) AS creator_name,
735 c.creator_id = user_.id) AS creator_avatar,
736 coalesce(sum(cl.score), 0) AS score,
738 CASE WHEN cl.score = 1 THEN
744 CASE WHEN cl.score = - 1 THEN
749 hot_rank (coalesce(sum(cl.score), 0), c.published) AS hot_rank
752 LEFT JOIN comment_like cl ON c.id = cl.comment_id
756 CREATE MATERIALIZED VIEW comment_aggregates_mview AS
760 comment_aggregates_view;
762 CREATE UNIQUE INDEX idx_comment_aggregates_mview_id ON comment_aggregates_mview (id);
764 CREATE VIEW comment_view AS
765 with all_comment AS (
769 comment_aggregates_view ca
774 coalesce(cl.score, 0) AS my_vote,
779 community_follower cf
782 AND ac.community_id = cf.community_id) AS subscribed,
790 AND cs.comment_id = ac.id) AS saved
793 CROSS JOIN all_comment ac
794 LEFT JOIN comment_like cl ON u.id = cl.user_id
795 AND ac.id = cl.comment_id
806 CREATE VIEW comment_mview AS
807 with all_comment AS (
811 comment_aggregates_mview ca
816 coalesce(cl.score, 0) AS my_vote,
821 community_follower cf
824 AND ac.community_id = cf.community_id) AS subscribed,
832 AND cs.comment_id = ac.id) AS saved
835 CROSS JOIN all_comment ac
836 LEFT JOIN comment_like cl ON u.id = cl.user_id
837 AND ac.id = cl.comment_id
848 -- Do the reply_view referencing the comment_mview
849 CREATE VIEW reply_view AS
853 c2.creator_id AS sender_id,
854 c.creator_id AS recipient_id
857 INNER JOIN comment c2 ON c.id = c2.parent_id
859 c2.creator_id != c.creator_id
860 -- Do union where post is null
864 c.creator_id AS sender_id,
865 p.creator_id AS recipient_id
871 AND c.parent_id IS NULL
872 AND c.creator_id != p.creator_id
876 closereply.recipient_id
881 closereply.id = cv.id;
884 CREATE VIEW user_mention_view AS
887 um.id AS user_mention_id,
900 c.community_actor_id,
904 c.banned_from_community,
921 u.id = um.recipient_id) AS recipient_actor_id,
928 u.id = um.recipient_id) AS recipient_local
933 um.comment_id = c.id;
935 CREATE VIEW user_mention_mview AS
936 with all_comment AS (
940 comment_aggregates_mview ca
944 um.id AS user_mention_id,
957 ac.community_actor_id,
961 ac.banned_from_community,
969 coalesce(cl.score, 0) AS my_vote,
977 AND cs.comment_id = ac.id) AS saved,
985 u.id = um.recipient_id) AS recipient_actor_id,
992 u.id = um.recipient_id) AS recipient_local
995 CROSS JOIN all_comment ac
996 LEFT JOIN comment_like cl ON u.id = cl.user_id
997 AND ac.id = cl.comment_id
998 LEFT JOIN user_mention um ON um.comment_id = ac.id
1002 um.id AS user_mention_id,
1004 ac.creator_actor_id,
1015 ac.community_actor_id,
1019 ac.banned_from_community,
1036 u.id = um.recipient_id) AS recipient_actor_id,
1043 u.id = um.recipient_id) AS recipient_local
1046 LEFT JOIN user_mention um ON um.comment_id = ac.id;