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,
6 post_score bigint not null,
7 comment_count bigint not null,
8 comment_score bigint not null,
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
39 create function user_aggregates_post_count()
40 returns trigger language plpgsql
43 IF (TG_OP = 'INSERT') THEN
44 update user_aggregates
45 set post_count = post_count + 1 where user_id = NEW.user_id;
46 ELSIF (TG_OP = 'DELETE') THEN
47 update user_aggregates
48 set post_count = post_count - 1 where user_id = OLD.user_id;
53 create trigger user_aggregates_post_count
54 after insert or delete on post
55 execute procedure user_aggregates_post_count();
58 create function user_aggregates_post_score()
59 returns trigger language plpgsql
62 IF (TG_OP = 'INSERT') THEN
63 -- TODO not sure if this is working right
64 -- Need to get the post creator, not the voter
65 update user_aggregates
66 set post_score = post_score + NEW.score
67 from post_like pl join post p on p.id = pl.post_id
68 where p.id = NEW.post_id and p.creator_id = NEW.user_id;
69 ELSIF (TG_OP = 'DELETE') THEN
70 update user_aggregates
71 set post_score = post_score - OLD.score
72 from post_like pl join post p on p.id = pl.post_id
73 where p.id = OLD.post_id and p.creator_id = OLD.user_id;
78 create trigger user_aggregates_post_score
79 after insert or delete on post_like
80 execute procedure user_aggregates_post_score();
83 create function user_aggregates_comment_count()
84 returns trigger language plpgsql
87 IF (TG_OP = 'INSERT') THEN
88 update user_aggregates
89 set comment_count = comment_count + 1 where user_id = NEW.user_id;
90 ELSIF (TG_OP = 'DELETE') THEN
91 update user_aggregates
92 set comment_count = comment_count - 1 where user_id = OLD.user_id;
97 create trigger user_aggregates_comment_count
98 after insert or delete on comment
99 execute procedure user_aggregates_comment_count();
102 create function user_aggregates_comment_score()
103 returns trigger language plpgsql
106 IF (TG_OP = 'INSERT') THEN
107 -- Need to get the post creator, not the voter
108 update user_aggregates
109 set comment_score = comment_score + NEW.score
110 from comment_like pl join comment p on p.id = pl.comment_id
111 where p.id = NEW.comment_id and p.creator_id = NEW.user_id;
112 ELSIF (TG_OP = 'DELETE') THEN
113 update user_aggregates
114 set comment_score = comment_score - OLD.score
115 from comment_like pl join comment p on p.id = pl.comment_id
116 where p.id = OLD.comment_id and p.creator_id = OLD.user_id;
121 create trigger user_aggregates_comment_score
122 after insert or delete on comment_like
123 execute procedure user_aggregates_comment_score();