4 CREATE VIEW post_view AS
14 p.creator_id = user_.id) AS creator_name,
21 p.community_id = community.id) AS community_name,
28 p.community_id = c.id) AS community_removed,
35 p.community_id = c.id) AS community_deleted,
42 p.community_id = c.id) AS community_nsfw,
49 comment.post_id = p.id) AS number_of_comments,
50 coalesce(sum(pl.score), 0) AS score,
52 CASE WHEN pl.score = 1 THEN
58 CASE WHEN pl.score = - 1 THEN
63 hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
66 LEFT JOIN post_like pl ON p.id = pl.post_id
73 coalesce(pl.score, 0) AS my_vote,
81 AND cf.community_id = ap.community_id) AS subscribed,
89 AND pr.post_id = ap.id) AS read,
97 AND ps.post_id = ap.id) AS saved
100 CROSS JOIN all_post ap
101 LEFT JOIN post_like pl ON u.id = pl.user_id
102 AND ap.id = pl.post_id