2 DROP VIEW user_mention_view;
6 DROP VIEW comment_view;
11 CREATE VIEW user_view AS
25 p.creator_id = u.id) AS number_of_posts,
28 coalesce(sum(score), 0)
34 AND p.id = pl.post_id) AS post_score,
41 c.creator_id = u.id) AS number_of_comments,
44 coalesce(sum(score), 0)
50 AND c.id = cl.comment_id) AS comment_score
58 CREATE VIEW post_view AS
68 p.creator_id = u.id) AS banned,
75 p.creator_id = cb.user_id
76 AND p.community_id = cb.community_id) AS banned_from_community,
83 p.creator_id = user_.id) AS creator_name,
90 p.community_id = community.id) AS community_name,
97 p.community_id = c.id) AS community_removed,
104 p.community_id = c.id) AS community_deleted,
111 p.community_id = c.id) AS community_nsfw,
118 comment.post_id = p.id) AS number_of_comments,
119 coalesce(sum(pl.score), 0) AS score,
121 CASE WHEN pl.score = 1 THEN
127 CASE WHEN pl.score = - 1 THEN
132 hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
135 LEFT JOIN post_like pl ON p.id = pl.post_id
142 coalesce(pl.score, 0) AS my_vote,
147 community_follower cf
150 AND cf.community_id = ap.community_id) AS subscribed,
158 AND pr.post_id = ap.id) AS read,
166 AND ps.post_id = ap.id) AS saved
169 CROSS JOIN all_post ap
170 LEFT JOIN post_like pl ON u.id = pl.user_id
171 AND ap.id = pl.post_id
184 DROP VIEW community_view;
186 CREATE VIEW community_view AS
187 with all_community AS (
196 c.creator_id = u.id) AS creator_name,
203 c.category_id = ct.id) AS category_name,
208 community_follower cf
210 cf.community_id = c.id) AS number_of_subscribers,
217 p.community_id = c.id) AS number_of_posts,
225 c.id = p.community_id
226 AND p.id = co.post_id) AS number_of_comments,
230 FROM community_follower cf
232 cf.community_id = c.id), c.published) AS hot_rank
243 community_follower cf
246 AND ac.id = cf.community_id) AS subscribed
249 CROSS JOIN all_community ac
258 -- Reply and comment view
259 CREATE VIEW comment_view AS
260 with all_comment AS (
276 c.creator_id = u.id) AS banned,
281 community_user_ban cb,
284 c.creator_id = cb.user_id
286 AND p.community_id = cb.community_id) AS banned_from_community,
293 c.creator_id = user_.id) AS creator_name,
294 coalesce(sum(cl.score), 0) AS score,
296 CASE WHEN cl.score = 1 THEN
302 CASE WHEN cl.score = - 1 THEN
309 LEFT JOIN comment_like cl ON c.id = cl.comment_id
316 coalesce(cl.score, 0) AS my_vote,
324 AND cs.comment_id = ac.id) AS saved
327 CROSS JOIN all_comment ac
328 LEFT JOIN comment_like cl ON u.id = cl.user_id
329 AND ac.id = cl.comment_id
339 CREATE VIEW reply_view AS
343 c2.creator_id AS sender_id,
344 c.creator_id AS recipient_id
347 INNER JOIN comment c2 ON c.id = c2.parent_id
349 c2.creator_id != c.creator_id
350 -- Do union where post is null
354 c.creator_id AS sender_id,
355 p.creator_id AS recipient_id
361 AND c.parent_id IS NULL
362 AND c.creator_id != p.creator_id
366 closereply.recipient_id
371 closereply.id = cv.id;
374 CREATE VIEW user_mention_view AS
377 um.id AS user_mention_id,
389 c.banned_from_community,
402 um.comment_id = c.id;
405 DROP VIEW community_moderator_view;
407 DROP VIEW community_follower_view;
409 DROP VIEW community_user_ban_view;
413 CREATE VIEW community_moderator_view AS
422 cm.user_id = u.id) AS user_name,
429 cm.community_id = c.id) AS community_name
431 community_moderator cm;
433 CREATE VIEW community_follower_view AS
442 cf.user_id = u.id) AS user_name,
449 cf.community_id = c.id) AS community_name
451 community_follower cf;
453 CREATE VIEW community_user_ban_view AS
462 cm.user_id = u.id) AS user_name,
469 cm.community_id = c.id) AS community_name
471 community_user_ban cm;
473 CREATE VIEW site_view AS
482 s.creator_id = u.id) AS creator_name,
487 user_) AS number_of_users,
492 post) AS number_of_posts,
497 comment) AS number_of_comments,
502 community) AS number_of_communities
506 ALTER TABLE user_ RENAME COLUMN avatar TO icon;
509 ALTER COLUMN icon TYPE bytea