3 ADD COLUMN embed_title text;
6 ADD COLUMN embed_description text;
9 ADD COLUMN embed_html text;
12 ADD COLUMN thumbnail_url text;
14 -- Regenerate the views
15 -- Adds a newest_activity_time for the post_views, in order to sort by newest comment
20 DROP MATERIALIZED VIEW post_aggregates_mview;
22 DROP VIEW post_aggregates_view;
25 CREATE VIEW post_aggregates_view AS
34 p.creator_id = u.id) AS banned,
41 p.creator_id = cb.user_id
42 AND p.community_id = cb.community_id) AS banned_from_community,
49 p.creator_id = user_.id) AS creator_name,
56 p.creator_id = user_.id) AS creator_avatar,
63 p.community_id = community.id) AS community_name,
70 p.community_id = c.id) AS community_removed,
77 p.community_id = c.id) AS community_deleted,
84 p.community_id = c.id) AS community_nsfw,
91 comment.post_id = p.id) AS number_of_comments,
92 coalesce(sum(pl.score), 0) AS score,
94 CASE WHEN pl.score = 1 THEN
100 CASE WHEN pl.score = - 1 THEN
105 hot_rank (coalesce(sum(pl.score), 0), (
106 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
107 p.published -- Prevents necro-bumps
109 greatest (c.recent_comment_time, p.published)
112 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
113 p.published -- Prevents necro-bumps
115 greatest (c.recent_comment_time, p.published)
116 END) AS newest_activity_time
119 LEFT JOIN post_like pl ON p.id = pl.post_id
123 max(published) AS recent_comment_time
127 1) c ON p.id = c.post_id
130 c.recent_comment_time;
132 CREATE MATERIALIZED VIEW post_aggregates_mview AS
136 post_aggregates_view;
138 CREATE UNIQUE INDEX idx_post_aggregates_mview_id ON post_aggregates_mview (id);
140 CREATE VIEW post_view AS
145 post_aggregates_view pa
150 coalesce(pl.score, 0) AS my_vote,
155 community_follower cf
158 AND cf.community_id = ap.community_id) AS subscribed,
166 AND pr.post_id = ap.id) AS read,
174 AND ps.post_id = ap.id) AS saved
177 CROSS JOIN all_post ap
178 LEFT JOIN post_like pl ON u.id = pl.user_id
179 AND ap.id = pl.post_id
191 CREATE VIEW post_mview AS
196 post_aggregates_mview pa
201 coalesce(pl.score, 0) AS my_vote,
206 community_follower cf
209 AND cf.community_id = ap.community_id) AS subscribed,
217 AND pr.post_id = ap.id) AS read,
225 AND ps.post_id = ap.id) AS saved
228 CROSS JOIN all_post ap
229 LEFT JOIN post_like pl ON u.id = pl.user_id
230 AND ap.id = pl.post_id