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 = pd.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 -- TODO not sure if this is working right
101 -- Need to get the post creator, not the voter
102 update user_aggregates ua
103 set post_score = post_score + NEW.score
105 where ua.user_id = p.creator_id and p.id = NEW.post_id;
107 ELSIF (TG_OP = 'DELETE') THEN
108 update user_aggregates ua
109 set post_score = post_score - OLD.score
111 where ua.user_id = p.creator_id and p.id = OLD.post_id;
116 create trigger user_aggregates_post_score
117 after insert or delete on post_like
119 execute procedure user_aggregates_post_score();
122 create function user_aggregates_comment_count()
123 returns trigger language plpgsql
126 IF (TG_OP = 'INSERT') THEN
127 update user_aggregates
128 set comment_count = comment_count + 1 where user_id = NEW.creator_id;
129 ELSIF (TG_OP = 'DELETE') THEN
130 update user_aggregates
131 set comment_count = comment_count - 1 where user_id = OLD.creator_id;
133 -- If the comment gets deleted, the score calculation trigger won't fire,
134 -- so you need to re-calculate
135 update user_aggregates ua
136 set comment_score = cd.score
139 coalesce(0, sum(cl.score)) as score
140 -- User join because comments could be empty
142 left join comment c on u.id = c.creator_id
143 left join comment_like cl on c.id = cl.comment_id
146 where ua.user_id = cd.id;
151 create trigger user_aggregates_comment_count
152 after insert or delete on comment
154 execute procedure user_aggregates_comment_count();
157 create function user_aggregates_comment_score()
158 returns trigger language plpgsql
161 IF (TG_OP = 'INSERT') THEN
162 -- Need to get the post creator, not the voter
163 update user_aggregates ua
164 set comment_score = comment_score + NEW.score
166 where ua.user_id = c.creator_id and c.id = NEW.comment_id;
167 ELSIF (TG_OP = 'DELETE') THEN
168 update user_aggregates ua
169 set comment_score = comment_score - OLD.score
171 where ua.user_id = c.creator_id and c.id = OLD.comment_id;
176 create trigger user_aggregates_comment_score
177 after insert or delete on comment_like
179 execute procedure user_aggregates_comment_score();