1 -- Adds a newest_activity_time for the post_views, in order to sort by newest comment
6 DROP MATERIALIZED VIEW post_aggregates_mview;
8 DROP VIEW post_aggregates_view;
12 DROP COLUMN embed_title;
15 DROP COLUMN embed_description;
18 DROP COLUMN embed_html;
21 DROP COLUMN thumbnail_url;
24 CREATE VIEW post_aggregates_view AS
33 p.creator_id = u.id) AS banned,
40 p.creator_id = cb.user_id
41 AND p.community_id = cb.community_id) AS banned_from_community,
48 p.creator_id = user_.id) AS creator_name,
55 p.creator_id = user_.id) AS creator_avatar,
62 p.community_id = community.id) AS community_name,
69 p.community_id = c.id) AS community_removed,
76 p.community_id = c.id) AS community_deleted,
83 p.community_id = c.id) AS community_nsfw,
90 comment.post_id = p.id) AS number_of_comments,
91 coalesce(sum(pl.score), 0) AS score,
93 CASE WHEN pl.score = 1 THEN
99 CASE WHEN pl.score = - 1 THEN
104 hot_rank (coalesce(sum(pl.score), 0), (
105 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
106 p.published -- Prevents necro-bumps
108 greatest (c.recent_comment_time, p.published)
111 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
112 p.published -- Prevents necro-bumps
114 greatest (c.recent_comment_time, p.published)
115 END) AS newest_activity_time
118 LEFT JOIN post_like pl ON p.id = pl.post_id
122 max(published) AS recent_comment_time
126 1) c ON p.id = c.post_id
129 c.recent_comment_time;
131 CREATE MATERIALIZED VIEW post_aggregates_mview AS
135 post_aggregates_view;
137 CREATE UNIQUE INDEX idx_post_aggregates_mview_id ON post_aggregates_mview (id);
139 CREATE VIEW post_view AS
144 post_aggregates_view pa
149 coalesce(pl.score, 0) AS my_vote,
154 community_follower cf
157 AND cf.community_id = ap.community_id) AS subscribed,
165 AND pr.post_id = ap.id) AS read,
173 AND ps.post_id = ap.id) AS saved
176 CROSS JOIN all_post ap
177 LEFT JOIN post_like pl ON u.id = pl.user_id
178 AND ap.id = pl.post_id
190 CREATE VIEW post_mview AS
195 post_aggregates_mview pa
200 coalesce(pl.score, 0) AS my_vote,
205 community_follower cf
208 AND cf.community_id = ap.community_id) AS subscribed,
216 AND pr.post_id = ap.id) AS read,
224 AND ps.post_id = ap.id) AS saved
227 CROSS JOIN all_post ap
228 LEFT JOIN post_like pl ON u.id = pl.user_id
229 AND ap.id = pl.post_id