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,
8 published timestamp not null default now(),
12 insert into community_aggregates (community_id, subscribers, posts, comments, published)
15 coalesce(cf.subs, 0) as subscribers,
16 coalesce(cd.posts, 0) as posts,
17 coalesce(cd.comments, 0) as comments,
23 count(distinct p.id) as posts,
24 count(distinct ct.id) as comments
26 left join comment ct on p.id = ct.post_id
27 group by p.community_id
28 ) cd on cd.community_id = c.id
31 community_follower.community_id,
33 from community_follower
34 group by community_follower.community_id
35 ) cf on cf.community_id = c.id;
37 -- Add community aggregate triggers
39 -- initial community add
40 create function community_aggregates_community()
41 returns trigger language plpgsql
44 IF (TG_OP = 'INSERT') THEN
45 insert into community_aggregates (community_id) values (NEW.id);
46 ELSIF (TG_OP = 'DELETE') THEN
47 delete from community_aggregates where community_id = OLD.id;
52 create trigger community_aggregates_community
53 after insert or delete on community
55 execute procedure community_aggregates_community();
57 create function community_aggregates_post_count()
58 returns trigger language plpgsql
61 IF (TG_OP = 'INSERT') THEN
62 update community_aggregates
63 set posts = posts + 1 where community_id = NEW.community_id;
64 ELSIF (TG_OP = 'DELETE') THEN
65 update community_aggregates
66 set posts = posts - 1 where community_id = OLD.community_id;
68 -- Update the counts if the post got deleted
69 update community_aggregates ca
70 set posts = coalesce(cd.posts, 0),
71 comments = coalesce(cd.comments, 0)
75 count(distinct p.id) as posts,
76 count(distinct ct.id) as comments
78 left join post p on c.id = p.community_id
79 left join comment ct on p.id = ct.post_id
82 where ca.community_id = OLD.community_id;
87 create trigger community_aggregates_post_count
88 after insert or delete on post
90 execute procedure community_aggregates_post_count();
93 create function community_aggregates_comment_count()
94 returns trigger language plpgsql
97 IF (TG_OP = 'INSERT') THEN
98 update community_aggregates ca
99 set comments = comments + 1 from comment c, post p
100 where p.id = c.post_id
101 and p.id = NEW.post_id
102 and ca.community_id = p.community_id;
103 ELSIF (TG_OP = 'DELETE') THEN
104 update community_aggregates ca
105 set comments = comments - 1 from comment c, post p
106 where p.id = c.post_id
107 and p.id = OLD.post_id
108 and ca.community_id = p.community_id;
114 create trigger community_aggregates_comment_count
115 after insert or delete on comment
117 execute procedure community_aggregates_comment_count();
120 create function community_aggregates_subscriber_count()
121 returns trigger language plpgsql
124 IF (TG_OP = 'INSERT') THEN
125 update community_aggregates
126 set subscribers = subscribers + 1 where community_id = NEW.community_id;
127 ELSIF (TG_OP = 'DELETE') THEN
128 update community_aggregates
129 set subscribers = subscribers - 1 where community_id = OLD.community_id;
134 create trigger community_aggregates_subscriber_count
135 after insert or delete on community_follower
137 execute procedure community_aggregates_subscriber_count();