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 default 0,
6 posts bigint not null default 0,
7 comments bigint not null default 0,
11 insert into community_aggregates (community_id, subscribers, posts, comments)
14 coalesce(cf.subs, 0) as subscribers,
15 coalesce(cd.posts, 0) as posts,
16 coalesce(cd.comments, 0) 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 -- initial community add
38 create function community_aggregates_community()
39 returns trigger language plpgsql
42 IF (TG_OP = 'INSERT') THEN
43 insert into community_aggregates (community_id) values (NEW.id);
44 ELSIF (TG_OP = 'DELETE') THEN
45 delete from community_aggregates where community_id = OLD.id;
50 create trigger community_aggregates_community
51 after insert or delete on community
53 execute procedure community_aggregates_community();
55 create function community_aggregates_post_count()
56 returns trigger language plpgsql
59 IF (TG_OP = 'INSERT') THEN
60 update community_aggregates
61 set posts = posts + 1 where community_id = NEW.community_id;
62 ELSIF (TG_OP = 'DELETE') THEN
63 update community_aggregates
64 set posts = posts - 1 where community_id = OLD.community_id;
66 -- Update the counts if the post got deleted
67 update community_aggregates ca
68 set posts = coalesce(cd.posts, 0),
69 comments = coalesce(cd.comments, 0)
73 count(distinct p.id) as posts,
74 count(distinct ct.id) as comments
76 left join post p on c.id = p.community_id
77 left join comment ct on p.id = ct.post_id
80 where ca.community_id = OLD.community_id;
85 create trigger community_aggregates_post_count
86 after insert or delete on post
88 execute procedure community_aggregates_post_count();
91 create function community_aggregates_comment_count()
92 returns trigger language plpgsql
95 IF (TG_OP = 'INSERT') THEN
96 update community_aggregates ca
97 set comments = comments + 1 from comment c, post p
98 where p.id = c.post_id
99 and p.id = NEW.post_id
100 and ca.community_id = p.community_id;
101 ELSIF (TG_OP = 'DELETE') THEN
102 update community_aggregates ca
103 set comments = comments - 1 from comment c, post p
104 where p.id = c.post_id
105 and p.id = OLD.post_id
106 and ca.community_id = p.community_id;
112 create trigger community_aggregates_comment_count
113 after insert or delete on comment
115 execute procedure community_aggregates_comment_count();
118 create function community_aggregates_subscriber_count()
119 returns trigger language plpgsql
122 IF (TG_OP = 'INSERT') THEN
123 update community_aggregates
124 set subscribers = subscribers + 1 where community_id = NEW.community_id;
125 ELSIF (TG_OP = 'DELETE') THEN
126 update community_aggregates
127 set subscribers = subscribers - 1 where community_id = OLD.community_id;
132 create trigger community_aggregates_subscriber_count
133 after insert or delete on community_follower
135 execute procedure community_aggregates_subscriber_count();