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;
11 CREATE VIEW post_aggregates_view AS
20 p.creator_id = u.id) AS banned,
27 p.creator_id = cb.user_id
28 AND p.community_id = cb.community_id) AS banned_from_community,
35 p.creator_id = user_.id) AS creator_name,
42 p.creator_id = user_.id) AS creator_avatar,
49 p.community_id = community.id) AS community_name,
56 p.community_id = c.id) AS community_removed,
63 p.community_id = c.id) AS community_deleted,
70 p.community_id = c.id) AS community_nsfw,
77 comment.post_id = p.id) AS number_of_comments,
78 coalesce(sum(pl.score), 0) AS score,
80 CASE WHEN pl.score = 1 THEN
86 CASE WHEN pl.score = - 1 THEN
91 hot_rank (coalesce(sum(pl.score), 0), (
92 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
93 p.published -- Prevents necro-bumps
95 greatest (c.recent_comment_time, p.published)
98 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
99 p.published -- Prevents necro-bumps
101 greatest (c.recent_comment_time, p.published)
102 END) AS newest_activity_time
105 LEFT JOIN post_like pl ON p.id = pl.post_id
109 max(published) AS recent_comment_time
113 1) c ON p.id = c.post_id
116 c.recent_comment_time;
118 CREATE MATERIALIZED VIEW post_aggregates_mview AS
122 post_aggregates_view;
124 CREATE UNIQUE INDEX idx_post_aggregates_mview_id ON post_aggregates_mview (id);
126 CREATE VIEW post_view AS
131 post_aggregates_view pa
136 coalesce(pl.score, 0) AS my_vote,
141 community_follower cf
144 AND cf.community_id = ap.community_id) AS subscribed,
152 AND pr.post_id = ap.id) AS read,
160 AND ps.post_id = ap.id) AS saved
163 CROSS JOIN all_post ap
164 LEFT JOIN post_like pl ON u.id = pl.user_id
165 AND ap.id = pl.post_id
177 CREATE VIEW post_mview AS
182 post_aggregates_mview pa
187 coalesce(pl.score, 0) AS my_vote,
192 community_follower cf
195 AND cf.community_id = ap.community_id) AS subscribed,
203 AND pr.post_id = ap.id) AS read,
211 AND ps.post_id = ap.id) AS saved
214 CROSS JOIN all_post ap
215 LEFT JOIN post_like pl ON u.id = pl.user_id
216 AND ap.id = pl.post_id