1 -- Drop the dependent views
6 DROP MATERIALIZED VIEW post_aggregates_mview;
8 DROP VIEW post_aggregates_view;
10 DROP VIEW mod_remove_post_view;
12 DROP VIEW mod_sticky_post_view;
14 DROP VIEW mod_lock_post_view;
16 DROP VIEW mod_remove_comment_view;
19 ALTER COLUMN name TYPE varchar(100);
22 CREATE VIEW post_aggregates_view AS
31 p.creator_id = u.id) AS banned,
38 p.creator_id = cb.user_id
39 AND p.community_id = cb.community_id) AS banned_from_community,
46 p.creator_id = user_.id) AS creator_name,
53 p.creator_id = user_.id) AS creator_avatar,
60 p.community_id = community.id) AS community_name,
67 p.community_id = c.id) AS community_removed,
74 p.community_id = c.id) AS community_deleted,
81 p.community_id = c.id) AS community_nsfw,
88 comment.post_id = p.id) AS number_of_comments,
89 coalesce(sum(pl.score), 0) AS score,
91 CASE WHEN pl.score = 1 THEN
97 CASE WHEN pl.score = - 1 THEN
102 hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
105 LEFT JOIN post_like pl ON p.id = pl.post_id
109 CREATE MATERIALIZED VIEW post_aggregates_mview AS
113 post_aggregates_view;
115 CREATE UNIQUE INDEX idx_post_aggregates_mview_id ON post_aggregates_mview (id);
117 CREATE VIEW post_view AS
122 post_aggregates_view pa
127 coalesce(pl.score, 0) AS my_vote,
132 community_follower cf
135 AND cf.community_id = ap.community_id) AS subscribed,
143 AND pr.post_id = ap.id) AS read,
151 AND ps.post_id = ap.id) AS saved
154 CROSS JOIN all_post ap
155 LEFT JOIN post_like pl ON u.id = pl.user_id
156 AND ap.id = pl.post_id
168 CREATE VIEW post_mview AS
173 post_aggregates_mview pa
178 coalesce(pl.score, 0) AS my_vote,
183 community_follower cf
186 AND cf.community_id = ap.community_id) AS subscribed,
194 AND pr.post_id = ap.id) AS read,
202 AND ps.post_id = ap.id) AS saved
205 CROSS JOIN all_post ap
206 LEFT JOIN post_like pl ON u.id = pl.user_id
207 AND ap.id = pl.post_id
220 CREATE VIEW mod_remove_post_view AS
229 mrp.mod_user_id = u.id) AS mod_user_name,
236 mrp.post_id = p.id) AS post_name,
245 AND p.community_id = c.id) AS community_id,
254 AND p.community_id = c.id) AS community_name
258 CREATE VIEW mod_lock_post_view AS
267 mlp.mod_user_id = u.id) AS mod_user_name,
274 mlp.post_id = p.id) AS post_name,
283 AND p.community_id = c.id) AS community_id,
292 AND p.community_id = c.id) AS community_name
296 CREATE VIEW mod_remove_comment_view AS
305 mrc.mod_user_id = u.id) AS mod_user_name,
312 mrc.comment_id = c.id) AS comment_user_id,
320 mrc.comment_id = c.id
321 AND u.id = c.creator_id) AS comment_user_name,
328 mrc.comment_id = c.id) AS comment_content,
336 mrc.comment_id = c.id
337 AND c.post_id = p.id) AS post_id,
345 mrc.comment_id = c.id
346 AND c.post_id = p.id) AS post_name,
355 mrc.comment_id = c.id
357 AND p.community_id = co.id) AS community_id,
366 mrc.comment_id = c.id
368 AND p.community_id = co.id) AS community_name
370 mod_remove_comment mrc;
372 CREATE VIEW mod_sticky_post_view AS
381 msp.mod_user_id = u.id) AS mod_user_name,
388 msp.post_id = p.id) AS post_name,
397 AND p.community_id = c.id) AS community_id,
406 AND p.community_id = c.id) AS community_name