1 -- Add community aggregates
2 create table community_aggregates (
4 community_id int references community on update cascade on delete cascade not null,
5 subscribers bigint not null,
7 comments bigint not null,
11 insert into community_aggregates (community_id, subscribers, posts, comments)
14 coalesce(cf.subs, 0::bigint) as subscribers,
15 coalesce(cd.posts, 0::bigint) as posts,
16 coalesce(cd.comments, 0::bigint) as comments
21 count(distinct p.id) as posts,
22 count(distinct ct.id) as comments
24 left join comment ct on p.id = ct.post_id
25 group by p.community_id
26 ) cd on cd.community_id = c.id
29 community_follower.community_id,
31 from community_follower
32 group by community_follower.community_id
33 ) cf on cf.community_id = c.id;
35 -- Add community aggregate triggers
37 create function community_aggregates_post_count()
38 returns trigger language plpgsql
41 IF (TG_OP = 'INSERT') THEN
42 update community_aggregates
43 set posts = posts + 1 where community_id = NEW.community_id;
44 ELSIF (TG_OP = 'DELETE') THEN
45 update community_aggregates
46 set posts = posts - 1 where community_id = OLD.community_id;
51 create trigger community_aggregates_post_count
52 after insert or delete on post
54 execute procedure community_aggregates_post_count();
57 create function community_aggregates_comment_count()
58 returns trigger language plpgsql
61 IF (TG_OP = 'INSERT') THEN
62 update community_aggregates
63 set comments = comments + 1 from comment c join post p on p.id = c.post_id and p.id = NEW.post_id;
64 ELSIF (TG_OP = 'DELETE') THEN
65 update community_aggregates
66 set comments = comments - 1 from comment c join post p on p.id = c.post_id and p.id = OLD.post_id;
71 create trigger community_aggregates_comment_count
72 after insert or delete on comment
74 execute procedure community_aggregates_comment_count();
77 create function community_aggregates_subscriber_count()
78 returns trigger language plpgsql
81 IF (TG_OP = 'INSERT') THEN
82 update community_aggregates
83 set subscribers = subscribers + 1 where community_id = NEW.community_id;
84 ELSIF (TG_OP = 'DELETE') THEN
85 update community_aggregates
86 set subscribers = subscribers - 1 where community_id = OLD.community_id;
91 create trigger community_aggregates_subscriber_count
92 after insert or delete on community_follower
94 execute procedure community_aggregates_subscriber_count();