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.creator_id;
46 ELSIF (TG_OP = 'DELETE') THEN
47 update user_aggregates
48 set post_count = post_count - 1 where user_id = OLD.creator_id;
53 create trigger user_aggregates_post_count
54 after insert or delete on post
56 execute procedure user_aggregates_post_count();
59 create function user_aggregates_post_score()
60 returns trigger language plpgsql
63 IF (TG_OP = 'INSERT') THEN
64 -- TODO not sure if this is working right
65 -- Need to get the post creator, not the voter
66 update user_aggregates
67 set post_score = post_score + NEW.score
68 from post_like pl join post p on p.id = pl.post_id
69 where p.id = NEW.post_id and p.creator_id = NEW.user_id;
70 ELSIF (TG_OP = 'DELETE') THEN
71 update user_aggregates
72 set post_score = post_score - OLD.score
73 from post_like pl join post p on p.id = pl.post_id
74 where p.id = OLD.post_id and p.creator_id = OLD.user_id;
79 create trigger user_aggregates_post_score
80 after insert or delete on post_like
82 execute procedure user_aggregates_post_score();
85 create function user_aggregates_comment_count()
86 returns trigger language plpgsql
89 IF (TG_OP = 'INSERT') THEN
90 update user_aggregates
91 set comment_count = comment_count + 1 where user_id = NEW.creator_id;
92 ELSIF (TG_OP = 'DELETE') THEN
93 update user_aggregates
94 set comment_count = comment_count - 1 where user_id = OLD.creator_id;
99 create trigger user_aggregates_comment_count
100 after insert or delete on comment
102 execute procedure user_aggregates_comment_count();
105 create function user_aggregates_comment_score()
106 returns trigger language plpgsql
109 IF (TG_OP = 'INSERT') THEN
110 -- Need to get the post creator, not the voter
111 update user_aggregates
112 set comment_score = comment_score + NEW.score
113 from comment_like pl join comment p on p.id = pl.comment_id
114 where p.id = NEW.comment_id and p.creator_id = NEW.user_id;
115 ELSIF (TG_OP = 'DELETE') THEN
116 update user_aggregates
117 set comment_score = comment_score - OLD.score
118 from comment_like pl join comment p on p.id = pl.comment_id
119 where p.id = OLD.comment_id and p.creator_id = OLD.user_id;
124 create trigger user_aggregates_comment_score
125 after insert or delete on comment_like
126 execute procedure user_aggregates_comment_score();