3 DROP VIEW mod_sticky_post_view;
8 DROP TABLE mod_sticky_post;
10 CREATE VIEW post_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.community_id = community.id) AS community_name,
49 p.community_id = c.id) AS community_removed,
56 p.community_id = c.id) AS community_deleted,
63 p.community_id = c.id) AS community_nsfw,
70 comment.post_id = p.id) AS number_of_comments,
71 coalesce(sum(pl.score), 0) AS score,
73 CASE WHEN pl.score = 1 THEN
79 CASE WHEN pl.score = - 1 THEN
84 hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
87 LEFT JOIN post_like pl ON p.id = pl.post_id
94 coalesce(pl.score, 0) AS my_vote,
102 AND cf.community_id = ap.community_id) AS subscribed,
110 AND pr.post_id = ap.id) AS read,
118 AND ps.post_id = ap.id) AS saved
121 CROSS JOIN all_post ap
122 LEFT JOIN post_like pl ON u.id = pl.user_id
123 AND ap.id = pl.post_id