1 -- Create post view, adding banned_from_community
4 CREATE VIEW post_view AS
14 p.creator_id = u.id) AS banned,
21 p.creator_id = cb.user_id
22 AND p.community_id = cb.community_id) AS banned_from_community,
29 p.creator_id = user_.id) AS creator_name,
36 p.community_id = community.id) AS community_name,
43 p.community_id = c.id) AS community_removed,
50 p.community_id = c.id) AS community_deleted,
57 p.community_id = c.id) AS community_nsfw,
64 comment.post_id = p.id) AS number_of_comments,
65 coalesce(sum(pl.score), 0) AS score,
67 CASE WHEN pl.score = 1 THEN
73 CASE WHEN pl.score = - 1 THEN
78 hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
81 LEFT JOIN post_like pl ON p.id = pl.post_id
88 coalesce(pl.score, 0) AS my_vote,
96 AND cf.community_id = ap.community_id) AS subscribed,
104 AND pr.post_id = ap.id) AS read,
112 AND ps.post_id = ap.id) AS saved
115 CROSS JOIN all_post ap
116 LEFT JOIN post_like pl ON u.id = pl.user_id
117 AND ap.id = pl.post_id