3 DROP VIEW user_mention_view;
5 DROP VIEW user_mention_mview;
7 DROP VIEW comment_view;
9 DROP VIEW comment_mview;
11 DROP MATERIALIZED VIEW comment_aggregates_mview;
13 DROP VIEW comment_aggregates_view;
15 -- reply and comment view
16 CREATE VIEW comment_aggregates_view AS
31 c.creator_id = u.id) AS banned,
36 community_user_ban cb,
39 c.creator_id = cb.user_id
41 AND p.community_id = cb.community_id) AS banned_from_community,
48 c.creator_id = user_.id) AS creator_name,
55 c.creator_id = user_.id) AS creator_avatar,
56 coalesce(sum(cl.score), 0) AS score,
58 CASE WHEN cl.score = 1 THEN
64 CASE WHEN cl.score = - 1 THEN
71 LEFT JOIN comment_like cl ON c.id = cl.comment_id
75 CREATE MATERIALIZED VIEW comment_aggregates_mview AS
79 comment_aggregates_view;
81 CREATE UNIQUE INDEX idx_comment_aggregates_mview_id ON comment_aggregates_mview (id);
83 CREATE VIEW comment_view AS
88 comment_aggregates_view ca
93 coalesce(cl.score, 0) AS my_vote,
101 AND cs.comment_id = ac.id) AS saved
104 CROSS JOIN all_comment ac
105 LEFT JOIN comment_like cl ON u.id = cl.user_id
106 AND ac.id = cl.comment_id
116 CREATE VIEW comment_mview AS
117 with all_comment AS (
121 comment_aggregates_mview ca
126 coalesce(cl.score, 0) AS my_vote,
134 AND cs.comment_id = ac.id) AS saved
137 CROSS JOIN all_comment ac
138 LEFT JOIN comment_like cl ON u.id = cl.user_id
139 AND ac.id = cl.comment_id
149 -- Do the reply_view referencing the comment_mview
150 CREATE VIEW reply_view AS
154 c2.creator_id AS sender_id,
155 c.creator_id AS recipient_id
158 INNER JOIN comment c2 ON c.id = c2.parent_id
160 c2.creator_id != c.creator_id
161 -- Do union where post is null
165 c.creator_id AS sender_id,
166 p.creator_id AS recipient_id
172 AND c.parent_id IS NULL
173 AND c.creator_id != p.creator_id
177 closereply.recipient_id
182 closereply.id = cv.id;
185 CREATE VIEW user_mention_view AS
188 um.id AS user_mention_id,
200 c.banned_from_community,
214 um.comment_id = c.id;
216 CREATE VIEW user_mention_mview AS
217 with all_comment AS (
221 comment_aggregates_mview ca
225 um.id AS user_mention_id,
237 ac.banned_from_community,
244 coalesce(cl.score, 0) AS my_vote,
252 AND cs.comment_id = ac.id) AS saved,
256 CROSS JOIN all_comment ac
257 LEFT JOIN comment_like cl ON u.id = cl.user_id
258 AND ac.id = cl.comment_id
259 LEFT JOIN user_mention um ON um.comment_id = ac.id
263 um.id AS user_mention_id,
275 ac.banned_from_community,
287 LEFT JOIN user_mention um ON um.comment_id = ac.id;