5 DROP MATERIALIZED VIEW post_aggregates_mview;
7 DROP VIEW post_aggregates_view;
10 CREATE VIEW post_aggregates_view AS
19 p.creator_id = u.id) AS banned,
26 p.creator_id = cb.user_id
27 AND p.community_id = cb.community_id) AS banned_from_community,
34 p.creator_id = user_.id) AS creator_name,
41 p.creator_id = user_.id) AS creator_avatar,
48 p.community_id = community.id) AS community_name,
55 p.community_id = c.id) AS community_removed,
62 p.community_id = c.id) AS community_deleted,
69 p.community_id = c.id) AS community_nsfw,
76 comment.post_id = p.id) AS number_of_comments,
77 coalesce(sum(pl.score), 0) AS score,
79 CASE WHEN pl.score = 1 THEN
85 CASE WHEN pl.score = - 1 THEN
90 hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
93 LEFT JOIN post_like pl ON p.id = pl.post_id
97 CREATE MATERIALIZED VIEW post_aggregates_mview AS
101 post_aggregates_view;
103 CREATE UNIQUE INDEX idx_post_aggregates_mview_id ON post_aggregates_mview (id);
105 CREATE VIEW post_view AS
110 post_aggregates_view pa
115 coalesce(pl.score, 0) AS my_vote,
120 community_follower cf
123 AND cf.community_id = ap.community_id) AS subscribed,
131 AND pr.post_id = ap.id) AS read,
139 AND ps.post_id = ap.id) AS saved
142 CROSS JOIN all_post ap
143 LEFT JOIN post_like pl ON u.id = pl.user_id
144 AND ap.id = pl.post_id
156 CREATE VIEW post_mview AS
161 post_aggregates_mview pa
166 coalesce(pl.score, 0) AS my_vote,
171 community_follower cf
174 AND cf.community_id = ap.community_id) AS subscribed,
182 AND pr.post_id = ap.id) AS read,
190 AND ps.post_id = ap.id) AS saved
193 CROSS JOIN all_post ap
194 LEFT JOIN post_like pl ON u.id = pl.user_id
195 AND ap.id = pl.post_id