2 CREATE TABLE user_aggregates (
4 user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
5 post_count bigint NOT NULL DEFAULT 0,
6 post_score bigint NOT NULL DEFAULT 0,
7 comment_count bigint NOT NULL DEFAULT 0,
8 comment_score bigint NOT NULL DEFAULT 0,
12 INSERT INTO user_aggregates (user_id, post_count, post_score, comment_count, comment_score)
15 coalesce(pd.posts, 0),
16 coalesce(pd.score, 0),
17 coalesce(cd.comments, 0),
24 count(DISTINCT p.id) AS posts,
25 sum(pl.score) AS score
28 LEFT JOIN post_like pl ON p.id = pl.post_id
30 p.creator_id) pd ON u.id = pd.creator_id
34 count(DISTINCT c.id) AS comments,
35 sum(cl.score) AS score
38 LEFT JOIN comment_like cl ON c.id = cl.comment_id
40 c.creator_id) cd ON u.id = cd.creator_id;
42 -- Add user aggregate triggers
44 CREATE FUNCTION user_aggregates_user ()
49 IF (TG_OP = 'INSERT') THEN
50 INSERT INTO user_aggregates (user_id)
52 ELSIF (TG_OP = 'DELETE') THEN
53 DELETE FROM user_aggregates
54 WHERE user_id = OLD.id;
60 CREATE TRIGGER user_aggregates_user
61 AFTER INSERT OR DELETE ON user_
63 EXECUTE PROCEDURE user_aggregates_user ();
66 CREATE FUNCTION user_aggregates_post_count ()
71 IF (TG_OP = 'INSERT') THEN
75 post_count = post_count + 1
77 user_id = NEW.creator_id;
78 ELSIF (TG_OP = 'DELETE') THEN
82 post_count = post_count - 1
84 user_id = OLD.creator_id;
85 -- If the post gets deleted, the score calculation trigger won't fire,
86 -- so you need to re-calculate
94 coalesce(0, sum(pl.score)) AS score
95 -- User join because posts could be empty
98 LEFT JOIN post p ON u.id = p.creator_id
99 LEFT JOIN post_like pl ON p.id = pl.post_id
103 ua.user_id = OLD.creator_id;
109 CREATE TRIGGER user_aggregates_post_count
110 AFTER INSERT OR DELETE ON post
112 EXECUTE PROCEDURE user_aggregates_post_count ();
115 CREATE FUNCTION user_aggregates_post_score ()
120 IF (TG_OP = 'INSERT') THEN
121 -- Need to get the post creator, not the voter
125 post_score = post_score + NEW.score
129 ua.user_id = p.creator_id
130 AND p.id = NEW.post_id;
131 ELSIF (TG_OP = 'DELETE') THEN
135 post_score = post_score - OLD.score
139 ua.user_id = p.creator_id
140 AND p.id = OLD.post_id;
146 CREATE TRIGGER user_aggregates_post_score
147 AFTER INSERT OR DELETE ON post_like
149 EXECUTE PROCEDURE user_aggregates_post_score ();
152 CREATE FUNCTION user_aggregates_comment_count ()
157 IF (TG_OP = 'INSERT') THEN
161 comment_count = comment_count + 1
163 user_id = NEW.creator_id;
164 ELSIF (TG_OP = 'DELETE') THEN
168 comment_count = comment_count - 1
170 user_id = OLD.creator_id;
171 -- If the comment gets deleted, the score calculation trigger won't fire,
172 -- so you need to re-calculate
176 comment_score = cd.score
180 coalesce(0, sum(cl.score)) AS score
181 -- User join because comments could be empty
184 LEFT JOIN comment c ON u.id = c.creator_id
185 LEFT JOIN comment_like cl ON c.id = cl.comment_id
189 ua.user_id = OLD.creator_id;
195 CREATE TRIGGER user_aggregates_comment_count
196 AFTER INSERT OR DELETE ON comment
198 EXECUTE PROCEDURE user_aggregates_comment_count ();
201 CREATE FUNCTION user_aggregates_comment_score ()
206 IF (TG_OP = 'INSERT') THEN
207 -- Need to get the post creator, not the voter
211 comment_score = comment_score + NEW.score
215 ua.user_id = c.creator_id
216 AND c.id = NEW.comment_id;
217 ELSIF (TG_OP = 'DELETE') THEN
221 comment_score = comment_score - OLD.score
225 ua.user_id = c.creator_id
226 AND c.id = OLD.comment_id;
232 CREATE TRIGGER user_aggregates_comment_score
233 AFTER INSERT OR DELETE ON comment_like
235 EXECUTE PROCEDURE user_aggregates_comment_score ();