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)
14 coalesce(pd.posts, 0),
15 coalesce(pd.score, 0),
16 coalesce(cd.comments, 0),
21 count(distinct p.id) as posts,
22 sum(pl.score) as score
24 left join post_like pl on p.id = pl.post_id
26 ) pd on u.id = pd.creator_id
29 count(distinct c.id) as comments,
30 sum(cl.score) as score
32 left join comment_like cl on c.id = cl.comment_id
34 ) cd on u.id = cd.creator_id;
37 -- Add user aggregate triggers
40 create function user_aggregates_user()
41 returns trigger language plpgsql
44 IF (TG_OP = 'INSERT') THEN
45 insert into user_aggregates (user_id) values (NEW.id);
46 ELSIF (TG_OP = 'DELETE') THEN
47 delete from user_aggregates where user_id = OLD.id;
52 create trigger user_aggregates_user
53 after insert or delete on user_
55 execute procedure user_aggregates_user();
58 create function user_aggregates_post_count()
59 returns trigger language plpgsql
62 IF (TG_OP = 'INSERT') THEN
63 update user_aggregates
64 set post_count = post_count + 1 where user_id = NEW.creator_id;
66 ELSIF (TG_OP = 'DELETE') THEN
67 update user_aggregates
68 set post_count = post_count - 1 where user_id = OLD.creator_id;
70 -- If the post gets deleted, the score calculation trigger won't fire,
71 -- so you need to re-calculate
72 update user_aggregates ua
73 set post_score = pd.score
76 coalesce(0, sum(pl.score)) as score
77 -- User join because posts could be empty
79 left join post p on u.id = p.creator_id
80 left join post_like pl on p.id = pl.post_id
83 where ua.user_id = OLD.creator_id;
89 create trigger user_aggregates_post_count
90 after insert or delete on post
92 execute procedure user_aggregates_post_count();
95 create function user_aggregates_post_score()
96 returns trigger language plpgsql
99 IF (TG_OP = 'INSERT') THEN
100 -- Need to get the post creator, not the voter
101 update user_aggregates ua
102 set post_score = post_score + NEW.score
104 where ua.user_id = p.creator_id and p.id = NEW.post_id;
106 ELSIF (TG_OP = 'DELETE') THEN
107 update user_aggregates ua
108 set post_score = post_score - OLD.score
110 where ua.user_id = p.creator_id and p.id = OLD.post_id;
115 create trigger user_aggregates_post_score
116 after insert or delete on post_like
118 execute procedure user_aggregates_post_score();
121 create function user_aggregates_comment_count()
122 returns trigger language plpgsql
125 IF (TG_OP = 'INSERT') THEN
126 update user_aggregates
127 set comment_count = comment_count + 1 where user_id = NEW.creator_id;
128 ELSIF (TG_OP = 'DELETE') THEN
129 update user_aggregates
130 set comment_count = comment_count - 1 where user_id = OLD.creator_id;
132 -- If the comment gets deleted, the score calculation trigger won't fire,
133 -- so you need to re-calculate
134 update user_aggregates ua
135 set comment_score = cd.score
138 coalesce(0, sum(cl.score)) as score
139 -- User join because comments could be empty
141 left join comment c on u.id = c.creator_id
142 left join comment_like cl on c.id = cl.comment_id
145 where ua.user_id = OLD.creator_id;
150 create trigger user_aggregates_comment_count
151 after insert or delete on comment
153 execute procedure user_aggregates_comment_count();
156 create function user_aggregates_comment_score()
157 returns trigger language plpgsql
160 IF (TG_OP = 'INSERT') THEN
161 -- Need to get the post creator, not the voter
162 update user_aggregates ua
163 set comment_score = comment_score + NEW.score
165 where ua.user_id = c.creator_id and c.id = NEW.comment_id;
166 ELSIF (TG_OP = 'DELETE') THEN
167 update user_aggregates ua
168 set comment_score = comment_score - OLD.score
170 where ua.user_id = c.creator_id and c.id = OLD.comment_id;
175 create trigger user_aggregates_comment_score
176 after insert or delete on comment_like
178 execute procedure user_aggregates_comment_score();