2 create view post_aggregates_view as
5 (select u.banned from user_ u where p.creator_id = u.id) as banned,
6 (select cb.id::bool from community_user_ban cb where p.creator_id = cb.user_id and p.community_id = cb.community_id) as banned_from_community,
7 (select name from user_ where p.creator_id = user_.id) as creator_name,
8 (select avatar from user_ where p.creator_id = user_.id) as creator_avatar,
9 (select name from community where p.community_id = community.id) as community_name,
10 (select removed from community c where p.community_id = c.id) as community_removed,
11 (select deleted from community c where p.community_id = c.id) as community_deleted,
12 (select nsfw from community c where p.community_id = c.id) as community_nsfw,
13 (select count(*) from comment where comment.post_id = p.id) as number_of_comments,
14 coalesce(sum(pl.score), 0) as score,
15 count (case when pl.score = 1 then 1 else null end) as upvotes,
16 count (case when pl.score = -1 then 1 else null end) as downvotes,
17 hot_rank(coalesce(sum(pl.score) , 0), p.published) as hot_rank
19 left join post_like pl on p.id = pl.post_id
22 create materialized view post_aggregates_mview as select * from post_aggregates_view;
24 create unique index idx_post_aggregates_mview_id on post_aggregates_mview (id);
27 create view post_view as
31 from post_aggregates_view pa
36 coalesce(pl.score, 0) as my_vote,
37 (select cf.id::bool from community_follower cf where u.id = cf.user_id and cf.community_id = ap.community_id) as subscribed,
38 (select pr.id::bool from post_read pr where u.id = pr.user_id and pr.post_id = ap.id) as read,
39 (select ps.id::bool from post_saved ps where u.id = ps.user_id and ps.post_id = ap.id) as saved
41 cross join all_post ap
42 left join post_like pl on u.id = pl.user_id and ap.id = pl.post_id
56 create view post_mview as
60 from post_aggregates_mview pa
65 coalesce(pl.score, 0) as my_vote,
66 (select cf.id::bool from community_follower cf where u.id = cf.user_id and cf.community_id = ap.community_id) as subscribed,
67 (select pr.id::bool from post_read pr where u.id = pr.user_id and pr.post_id = ap.id) as read,
68 (select ps.id::bool from post_saved ps where u.id = ps.user_id and ps.post_id = ap.id) as saved
70 cross join all_post ap
71 left join post_like pl on u.id = pl.user_id and ap.id = pl.post_id
88 create view user_view as
98 u.send_notifications_to_email,
100 (select count(*) from post p where p.creator_id = u.id) as number_of_posts,
101 (select coalesce(sum(score), 0) from post p, post_like pl where u.id = p.creator_id and p.id = pl.post_id) as post_score,
102 (select count(*) from comment c where c.creator_id = u.id) as number_of_comments,
103 (select coalesce(sum(score), 0) from comment c, comment_like cl where u.id = c.creator_id and c.id = cl.comment_id) as comment_score
106 create materialized view user_mview as select * from user_view;
108 create unique index idx_user_mview_id on user_mview (id);
111 create view community_aggregates_view as
113 (select name from user_ u where c.creator_id = u.id) as creator_name,
114 (select avatar from user_ u where c.creator_id = u.id) as creator_avatar,
115 (select name from category ct where c.category_id = ct.id) as category_name,
116 (select count(*) from community_follower cf where cf.community_id = c.id) as number_of_subscribers,
117 (select count(*) from post p where p.community_id = c.id) as number_of_posts,
118 (select count(*) from comment co, post p where c.id = p.community_id and p.id = co.post_id) as number_of_comments,
119 hot_rank((select count(*) from community_follower cf where cf.community_id = c.id), c.published) as hot_rank
122 create materialized view community_aggregates_mview as select * from community_aggregates_view;
124 create unique index idx_community_aggregates_mview_id on community_aggregates_mview (id);
126 drop view community_view;
127 create view community_view as
128 with all_community as
132 from community_aggregates_view ca
138 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
140 cross join all_community ac
148 from all_community ac
151 create view community_mview as
152 with all_community as
156 from community_aggregates_mview ca
162 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
164 cross join all_community ac
172 from all_community ac
176 -- reply and comment view
177 create view comment_aggregates_view as
180 (select community_id from post p where p.id = c.post_id),
181 (select u.banned from user_ u where c.creator_id = u.id) as banned,
182 (select cb.id::bool from community_user_ban cb, post p where c.creator_id = cb.user_id and p.id = c.post_id and p.community_id = cb.community_id) as banned_from_community,
183 (select name from user_ where c.creator_id = user_.id) as creator_name,
184 (select avatar from user_ where c.creator_id = user_.id) as creator_avatar,
185 coalesce(sum(cl.score), 0) as score,
186 count (case when cl.score = 1 then 1 else null end) as upvotes,
187 count (case when cl.score = -1 then 1 else null end) as downvotes
189 left join comment_like cl on c.id = cl.comment_id
192 create materialized view comment_aggregates_mview as select * from comment_aggregates_view;
194 create unique index idx_comment_aggregates_mview_id on comment_aggregates_mview (id);
196 drop view reply_view;
197 drop view user_mention_view;
198 drop view comment_view;
200 create view comment_view as
205 from comment_aggregates_view ca
211 coalesce(cl.score, 0) as my_vote,
212 (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved
214 cross join all_comment ac
215 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
227 create view comment_mview as
232 from comment_aggregates_mview ca
238 coalesce(cl.score, 0) as my_vote,
239 (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved
241 cross join all_comment ac
242 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
254 create view reply_view as
258 c2.creator_id as sender_id,
259 c.creator_id as recipient_id
261 inner join comment c2 on c.id = c2.parent_id
262 where c2.creator_id != c.creator_id
263 -- Do union where post is null
267 c.creator_id as sender_id,
268 p.creator_id as recipient_id
269 from comment c, post p
270 where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id
273 closereply.recipient_id
274 from comment_view cv, closereply
275 where closereply.id = cv.id
279 create view user_mention_view as
282 um.id as user_mention_id,
294 c.banned_from_community,
304 from user_mention um, comment_view c
305 where um.comment_id = c.id;
308 create or replace function refresh_user()
309 returns trigger language plpgsql
312 refresh materialized view concurrently user_mview;
313 refresh materialized view concurrently comment_aggregates_mview; -- cause of bans
314 refresh materialized view concurrently post_aggregates_mview;
318 create trigger refresh_user
319 after insert or update or delete or truncate
322 execute procedure refresh_user();
325 create or replace function refresh_post()
326 returns trigger language plpgsql
329 refresh materialized view concurrently post_aggregates_mview;
330 refresh materialized view concurrently user_mview;
334 create trigger refresh_post
335 after insert or update or delete or truncate
338 execute procedure refresh_post();
341 create or replace function refresh_post_like()
342 returns trigger language plpgsql
345 refresh materialized view concurrently post_aggregates_mview;
346 refresh materialized view concurrently user_mview;
350 create trigger refresh_post_like
351 after insert or update or delete or truncate
354 execute procedure refresh_post_like();
357 create or replace function refresh_community()
358 returns trigger language plpgsql
361 refresh materialized view concurrently post_aggregates_mview;
362 refresh materialized view concurrently community_aggregates_mview;
363 refresh materialized view concurrently user_mview;
367 create trigger refresh_community
368 after insert or update or delete or truncate
371 execute procedure refresh_community();
373 -- community_follower
374 create or replace function refresh_community_follower()
375 returns trigger language plpgsql
378 refresh materialized view concurrently community_aggregates_mview;
379 refresh materialized view concurrently post_aggregates_mview;
383 create trigger refresh_community_follower
384 after insert or update or delete or truncate
385 on community_follower
387 execute procedure refresh_community_follower();
389 -- community_user_ban
390 create or replace function refresh_community_user_ban()
391 returns trigger language plpgsql
394 refresh materialized view concurrently comment_aggregates_mview;
395 refresh materialized view concurrently post_aggregates_mview;
399 create trigger refresh_community_user_ban
400 after insert or update or delete or truncate
401 on community_user_ban
403 execute procedure refresh_community_user_ban();
406 create or replace function refresh_comment()
407 returns trigger language plpgsql
410 refresh materialized view concurrently post_aggregates_mview;
411 refresh materialized view concurrently comment_aggregates_mview;
412 refresh materialized view concurrently community_aggregates_mview;
413 refresh materialized view concurrently user_mview;
417 create trigger refresh_comment
418 after insert or update or delete or truncate
421 execute procedure refresh_comment();
424 create or replace function refresh_comment_like()
425 returns trigger language plpgsql
428 refresh materialized view concurrently comment_aggregates_mview;
429 refresh materialized view concurrently user_mview;
433 create trigger refresh_comment_like
434 after insert or update or delete or truncate
437 execute procedure refresh_comment_like();