3 ADD COLUMN stickied boolean DEFAULT FALSE NOT NULL;
6 CREATE TABLE mod_sticky_post (
8 mod_user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
9 post_id int REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
10 stickied boolean DEFAULT TRUE,
11 when_ timestamp NOT NULL DEFAULT now()
15 CREATE VIEW mod_sticky_post_view AS
24 msp.mod_user_id = u.id) AS mod_user_name,
31 msp.post_id = p.id) AS post_name,
40 AND p.community_id = c.id) AS community_id,
49 AND p.community_id = c.id) AS community_name
56 CREATE VIEW post_view AS
66 p.creator_id = u.id) AS banned,
73 p.creator_id = cb.user_id
74 AND p.community_id = cb.community_id) AS banned_from_community,
81 p.creator_id = user_.id) AS creator_name,
88 p.community_id = community.id) AS community_name,
95 p.community_id = c.id) AS community_removed,
102 p.community_id = c.id) AS community_deleted,
109 p.community_id = c.id) AS community_nsfw,
116 comment.post_id = p.id) AS number_of_comments,
117 coalesce(sum(pl.score), 0) AS score,
119 CASE WHEN pl.score = 1 THEN
125 CASE WHEN pl.score = - 1 THEN
130 hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
133 LEFT JOIN post_like pl ON p.id = pl.post_id
140 coalesce(pl.score, 0) AS my_vote,
145 community_follower cf
148 AND cf.community_id = ap.community_id) AS subscribed,
156 AND pr.post_id = ap.id) AS read,
164 AND ps.post_id = ap.id) AS saved
167 CROSS JOIN all_post ap
168 LEFT JOIN post_like pl ON u.id = pl.user_id
169 AND ap.id = pl.post_id