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
53 execute procedure community_aggregates_post_count();
56 create function community_aggregates_comment_count()
57 returns trigger language plpgsql
60 IF (TG_OP = 'INSERT') THEN
61 update community_aggregates
62 set comments = comments + 1 where community_id = NEW.community_id;
63 ELSIF (TG_OP = 'DELETE') THEN
64 update community_aggregates
65 set comments = comments - 1 where community_id = OLD.community_id;
70 create trigger community_aggregates_comment_count
71 after insert or delete on comment
72 execute procedure community_aggregates_comment_count();
75 create function community_aggregates_subscriber_count()
76 returns trigger language plpgsql
79 IF (TG_OP = 'INSERT') THEN
80 update community_aggregates
81 set subscribers = subscribers + 1 where community_id = NEW.community_id;
82 ELSIF (TG_OP = 'DELETE') THEN
83 update community_aggregates
84 set subscribers = subscribers - 1 where community_id = OLD.community_id;
89 create trigger community_aggregates_subscriber_count
90 after insert or delete on community_follower
91 execute procedure community_aggregates_subscriber_count();