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
20 c.actor_id AS community_actor_id,
21 c."local" AS community_local,
22 c."name" AS community_name,
25 coalesce(cb.id, 0)::bool AS banned_from_community,
26 u.actor_id AS creator_actor_id,
27 u.local AS creator_local,
28 u.name AS creator_name,
29 u.published AS creator_published,
30 u.avatar AS creator_avatar,
32 coalesce(cl.total, 0) AS score,
33 coalesce(cl.up, 0) AS upvotes,
34 coalesce(cl.down, 0) AS downvotes,
35 hot_rank (coalesce(cl.total, 0), ct.published) AS hot_rank
38 LEFT JOIN post p ON ct.post_id = p.id
39 LEFT JOIN community c ON p.community_id = c.id
40 LEFT JOIN user_ u ON ct.creator_id = u.id
41 LEFT JOIN community_user_ban cb ON ct.creator_id = cb.user_id
43 AND p.community_id = cb.community_id
47 sum(l.score) AS total,
49 CASE WHEN l.score = 1 THEN
55 CASE WHEN l.score = - 1 THEN
63 comment_id) AS cl ON cl.id = ct.id;
65 CREATE OR REPLACE VIEW comment_view AS (
68 us.user_id AS user_id,
69 us.my_vote AS my_vote,
70 us.is_subbed::bool AS subscribed,
71 us.is_saved::bool AS saved
73 comment_aggregates_view cav
77 coalesce(cl.score, 0) AS my_vote,
78 coalesce(cf.id, 0) AS is_subbed,
79 coalesce(cs.id, 0) AS is_saved
82 LEFT JOIN comment_like cl ON u.id = cl.user_id
83 AND cav.id = cl.comment_id
84 LEFT JOIN comment_saved cs ON u.id = cs.user_id
85 AND cs.comment_id = cav.id
86 LEFT JOIN community_follower cf ON u.id = cf.user_id
87 AND cav.community_id = cf.community_id) AS us
96 comment_aggregates_view cav);
98 CREATE TABLE comment_aggregates_fast AS
102 comment_aggregates_view;
104 ALTER TABLE comment_aggregates_fast
105 ADD PRIMARY KEY (id);
107 CREATE VIEW comment_fast_view AS
110 us.user_id AS user_id,
111 us.my_vote AS my_vote,
112 us.is_subbed::bool AS subscribed,
113 us.is_saved::bool AS saved
115 comment_aggregates_fast cav
119 coalesce(cl.score, 0) AS my_vote,
120 coalesce(cf.id, 0) AS is_subbed,
121 coalesce(cs.id, 0) AS is_saved
124 LEFT JOIN comment_like cl ON u.id = cl.user_id
125 AND cav.id = cl.comment_id
126 LEFT JOIN comment_saved cs ON u.id = cs.user_id
127 AND cs.comment_id = cav.id
128 LEFT JOIN community_follower cf ON u.id = cf.user_id
129 AND cav.community_id = cf.community_id) AS us
138 comment_aggregates_fast cav;
140 CREATE VIEW user_mention_view AS
143 um.id AS user_mention_id,
156 c.community_actor_id,
160 c.banned_from_community,
177 u.id = um.recipient_id) AS recipient_actor_id,
184 u.id = um.recipient_id) AS recipient_local
189 um.comment_id = c.id;
191 CREATE VIEW user_mention_fast_view AS
194 um.id AS user_mention_id,
207 ac.community_actor_id,
211 ac.banned_from_community,
219 coalesce(cl.score, 0) AS my_vote,
227 AND cs.comment_id = ac.id) AS saved,
235 u.id = um.recipient_id) AS recipient_actor_id,
242 u.id = um.recipient_id) AS recipient_local
249 comment_aggregates_fast ca) ac
250 LEFT JOIN comment_like cl ON u.id = cl.user_id
251 AND ac.id = cl.comment_id
252 LEFT JOIN user_mention um ON um.comment_id = ac.id
256 um.id AS user_mention_id,
269 ac.community_actor_id,
273 ac.banned_from_community,
290 u.id = um.recipient_id) AS recipient_actor_id,
297 u.id = um.recipient_id) AS recipient_local
299 comment_aggregates_fast ac
300 LEFT JOIN user_mention um ON um.comment_id = ac.id;
302 -- Do the reply_view referencing the comment_fast_view
303 CREATE VIEW reply_fast_view AS
307 c2.creator_id AS sender_id,
308 c.creator_id AS recipient_id
311 INNER JOIN comment c2 ON c.id = c2.parent_id
313 c2.creator_id != c.creator_id
314 -- Do union where post is null
318 c.creator_id AS sender_id,
319 p.creator_id AS recipient_id
325 AND c.parent_id IS NULL
326 AND c.creator_id != p.creator_id
330 closereply.recipient_id
332 comment_fast_view cv,
335 closereply.id = cv.id;