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;
18 -- Add the extra post limit
20 ALTER COLUMN name TYPE varchar(200);
23 CREATE VIEW post_aggregates_view AS
32 p.creator_id = u.id) AS banned,
39 p.creator_id = cb.user_id
40 AND p.community_id = cb.community_id) AS banned_from_community,
47 p.creator_id = user_.id) AS creator_name,
54 p.creator_id = user_.id) AS creator_avatar,
61 p.community_id = community.id) AS community_name,
68 p.community_id = c.id) AS community_removed,
75 p.community_id = c.id) AS community_deleted,
82 p.community_id = c.id) AS community_nsfw,
89 comment.post_id = p.id) AS number_of_comments,
90 coalesce(sum(pl.score), 0) AS score,
92 CASE WHEN pl.score = 1 THEN
98 CASE WHEN pl.score = - 1 THEN
103 hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
106 LEFT JOIN post_like pl ON p.id = pl.post_id
110 CREATE MATERIALIZED VIEW post_aggregates_mview AS
114 post_aggregates_view;
116 CREATE UNIQUE INDEX idx_post_aggregates_mview_id ON post_aggregates_mview (id);
118 CREATE VIEW post_view AS
123 post_aggregates_view pa
128 coalesce(pl.score, 0) AS my_vote,
133 community_follower cf
136 AND cf.community_id = ap.community_id) AS subscribed,
144 AND pr.post_id = ap.id) AS read,
152 AND ps.post_id = ap.id) AS saved
155 CROSS JOIN all_post ap
156 LEFT JOIN post_like pl ON u.id = pl.user_id
157 AND ap.id = pl.post_id
169 CREATE VIEW post_mview AS
174 post_aggregates_mview pa
179 coalesce(pl.score, 0) AS my_vote,
184 community_follower cf
187 AND cf.community_id = ap.community_id) AS subscribed,
195 AND pr.post_id = ap.id) AS read,
203 AND ps.post_id = ap.id) AS saved
206 CROSS JOIN all_post ap
207 LEFT JOIN post_like pl ON u.id = pl.user_id
208 AND ap.id = pl.post_id
221 CREATE VIEW mod_remove_post_view AS
230 mrp.mod_user_id = u.id) AS mod_user_name,
237 mrp.post_id = p.id) AS post_name,
246 AND p.community_id = c.id) AS community_id,
255 AND p.community_id = c.id) AS community_name
259 CREATE VIEW mod_lock_post_view AS
268 mlp.mod_user_id = u.id) AS mod_user_name,
275 mlp.post_id = p.id) AS post_name,
284 AND p.community_id = c.id) AS community_id,
293 AND p.community_id = c.id) AS community_name
297 CREATE VIEW mod_remove_comment_view AS
306 mrc.mod_user_id = u.id) AS mod_user_name,
313 mrc.comment_id = c.id) AS comment_user_id,
321 mrc.comment_id = c.id
322 AND u.id = c.creator_id) AS comment_user_name,
329 mrc.comment_id = c.id) AS comment_content,
337 mrc.comment_id = c.id
338 AND c.post_id = p.id) AS post_id,
346 mrc.comment_id = c.id
347 AND c.post_id = p.id) AS post_name,
356 mrc.comment_id = c.id
358 AND p.community_id = co.id) AS community_id,
367 mrc.comment_id = c.id
369 AND p.community_id = co.id) AS community_name
371 mod_remove_comment mrc;
373 CREATE VIEW mod_sticky_post_view AS
382 msp.mod_user_id = u.id) AS mod_user_name,
389 msp.post_id = p.id) AS post_name,
398 AND p.community_id = c.id) AS community_id,
407 AND p.community_id = c.id) AS community_name