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,
24 count(DISTINCT p.id) AS posts,
25 count(DISTINCT ct.id) AS comments
28 LEFT JOIN comment ct ON p.id = ct.post_id
30 p.community_id) cd ON cd.community_id = c.id
33 community_follower.community_id,
38 community_follower.community_id) cf ON cf.community_id = c.id;
40 -- Add community aggregate triggers
41 -- initial community add
42 CREATE FUNCTION community_aggregates_community ()
47 IF (TG_OP = 'INSERT') THEN
48 INSERT INTO community_aggregates (community_id)
50 ELSIF (TG_OP = 'DELETE') THEN
51 DELETE FROM community_aggregates
52 WHERE community_id = OLD.id;
58 CREATE TRIGGER community_aggregates_community
59 AFTER INSERT OR DELETE ON community
61 EXECUTE PROCEDURE community_aggregates_community ();
64 CREATE FUNCTION community_aggregates_post_count ()
69 IF (TG_OP = 'INSERT') THEN
75 community_id = NEW.community_id;
76 ELSIF (TG_OP = 'DELETE') THEN
82 community_id = OLD.community_id;
83 -- Update the counts if the post got deleted
85 community_aggregates ca
87 posts = coalesce(cd.posts, 0),
88 comments = coalesce(cd.comments, 0)
92 count(DISTINCT p.id) AS posts,
93 count(DISTINCT ct.id) AS comments
96 LEFT JOIN post p ON c.id = p.community_id
97 LEFT JOIN comment ct ON p.id = ct.post_id
101 ca.community_id = OLD.community_id;
107 CREATE TRIGGER community_aggregates_post_count
108 AFTER INSERT OR DELETE ON post
110 EXECUTE PROCEDURE community_aggregates_post_count ();
113 CREATE FUNCTION community_aggregates_comment_count ()
118 IF (TG_OP = 'INSERT') THEN
120 community_aggregates ca
122 comments = comments + 1
128 AND p.id = NEW.post_id
129 AND ca.community_id = p.community_id;
130 ELSIF (TG_OP = 'DELETE') THEN
132 community_aggregates ca
134 comments = comments - 1
140 AND p.id = OLD.post_id
141 AND ca.community_id = p.community_id;
147 CREATE TRIGGER community_aggregates_comment_count
148 AFTER INSERT OR DELETE ON comment
150 EXECUTE PROCEDURE community_aggregates_comment_count ();
153 CREATE FUNCTION community_aggregates_subscriber_count ()
158 IF (TG_OP = 'INSERT') THEN
162 subscribers = subscribers + 1
164 community_id = NEW.community_id;
165 ELSIF (TG_OP = 'DELETE') THEN
169 subscribers = subscribers - 1
171 community_id = OLD.community_id;
177 CREATE TRIGGER community_aggregates_subscriber_count
178 AFTER INSERT OR DELETE ON community_follower
180 EXECUTE PROCEDURE community_aggregates_subscriber_count ();