1 DROP VIEW user_mention_view;
3 DROP VIEW reply_fast_view;
5 DROP VIEW comment_fast_view;
7 DROP VIEW comment_view;
9 DROP VIEW user_mention_fast_view;
11 DROP TABLE comment_aggregates_fast;
13 DROP VIEW comment_aggregates_view;
15 CREATE VIEW comment_aggregates_view AS
19 p."name" AS post_name,
22 c.actor_id AS community_actor_id,
23 c."local" AS community_local,
24 c."name" AS community_name,
27 coalesce(cb.id, 0)::bool AS banned_from_community,
28 u.actor_id AS creator_actor_id,
29 u.local AS creator_local,
30 u.name AS creator_name,
31 u.published AS creator_published,
32 u.avatar AS creator_avatar,
34 coalesce(cl.total, 0) AS score,
35 coalesce(cl.up, 0) AS upvotes,
36 coalesce(cl.down, 0) AS downvotes,
37 hot_rank (coalesce(cl.total, 0), ct.published) AS hot_rank
40 LEFT JOIN post p ON ct.post_id = p.id
41 LEFT JOIN community c ON p.community_id = c.id
42 LEFT JOIN user_ u ON ct.creator_id = u.id
43 LEFT JOIN community_user_ban cb ON ct.creator_id = cb.user_id
45 AND p.community_id = cb.community_id
49 sum(l.score) AS total,
51 CASE WHEN l.score = 1 THEN
57 CASE WHEN l.score = - 1 THEN
65 comment_id) AS cl ON cl.id = ct.id;
67 CREATE OR REPLACE VIEW comment_view AS (
70 us.user_id AS user_id,
71 us.my_vote AS my_vote,
72 us.is_subbed::bool AS subscribed,
73 us.is_saved::bool AS saved
75 comment_aggregates_view cav
79 coalesce(cl.score, 0) AS my_vote,
80 coalesce(cf.id, 0) AS is_subbed,
81 coalesce(cs.id, 0) AS is_saved
84 LEFT JOIN comment_like cl ON u.id = cl.user_id
85 AND cav.id = cl.comment_id
86 LEFT JOIN comment_saved cs ON u.id = cs.user_id
87 AND cs.comment_id = cav.id
88 LEFT JOIN community_follower cf ON u.id = cf.user_id
89 AND cav.community_id = cf.community_id) AS us
98 comment_aggregates_view cav);
100 CREATE TABLE comment_aggregates_fast AS
104 comment_aggregates_view;
106 ALTER TABLE comment_aggregates_fast
107 ADD PRIMARY KEY (id);
109 CREATE VIEW comment_fast_view AS
112 us.user_id AS user_id,
113 us.my_vote AS my_vote,
114 us.is_subbed::bool AS subscribed,
115 us.is_saved::bool AS saved
117 comment_aggregates_fast cav
121 coalesce(cl.score, 0) AS my_vote,
122 coalesce(cf.id, 0) AS is_subbed,
123 coalesce(cs.id, 0) AS is_saved
126 LEFT JOIN comment_like cl ON u.id = cl.user_id
127 AND cav.id = cl.comment_id
128 LEFT JOIN comment_saved cs ON u.id = cs.user_id
129 AND cs.comment_id = cav.id
130 LEFT JOIN community_follower cf ON u.id = cf.user_id
131 AND cav.community_id = cf.community_id) AS us
140 comment_aggregates_fast cav;
142 CREATE VIEW user_mention_view AS
145 um.id AS user_mention_id,
159 c.community_actor_id,
163 c.banned_from_community,
180 u.id = um.recipient_id) AS recipient_actor_id,
187 u.id = um.recipient_id) AS recipient_local
192 um.comment_id = c.id;
194 CREATE VIEW user_mention_fast_view AS
197 um.id AS user_mention_id,
211 ac.community_actor_id,
215 ac.banned_from_community,
223 coalesce(cl.score, 0) AS my_vote,
231 AND cs.comment_id = ac.id) AS saved,
239 u.id = um.recipient_id) AS recipient_actor_id,
246 u.id = um.recipient_id) AS recipient_local
253 comment_aggregates_fast ca) ac
254 LEFT JOIN comment_like cl ON u.id = cl.user_id
255 AND ac.id = cl.comment_id
256 LEFT JOIN user_mention um ON um.comment_id = ac.id
260 um.id AS user_mention_id,
274 ac.community_actor_id,
278 ac.banned_from_community,
295 u.id = um.recipient_id) AS recipient_actor_id,
302 u.id = um.recipient_id) AS recipient_local
304 comment_aggregates_fast ac
305 LEFT JOIN user_mention um ON um.comment_id = ac.id;
307 -- Do the reply_view referencing the comment_fast_view
308 CREATE VIEW reply_fast_view AS
312 c2.creator_id AS sender_id,
313 c.creator_id AS recipient_id
316 INNER JOIN comment c2 ON c.id = c2.parent_id
318 c2.creator_id != c.creator_id
319 -- Do union where post is null
323 c.creator_id AS sender_id,
324 p.creator_id AS recipient_id
330 AND c.parent_id IS NULL
331 AND c.creator_id != p.creator_id
335 closereply.recipient_id
337 comment_fast_view cv,
340 closereply.id = cv.id;