migrations/2020-12-03-035643_create_user_aggregates/up.sql

   1 -- Add user aggregates
   2 create table user_aggregates (
   3   id serial primary key,
   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,
   9   unique (user_id)
  10 );
  11
  12 insert into user_aggregates (user_id, post_count, post_score, comment_count, comment_score)
  13   select u.id,
  14   coalesce(pd.posts, 0),
  15   coalesce(pd.score, 0),
  16   coalesce(cd.comments, 0),
  17   coalesce(cd.score, 0)
  18   from user_ u
  19   left join (
  20     select p.creator_id,
  21       count(distinct p.id) as posts,
  22       sum(pl.score) as score
  23       from post p
  24       left join post_like pl on p.id = pl.post_id
  25       group by p.creator_id
  26     ) pd on u.id = pd.creator_id
  27   left join (
  28     select c.creator_id,
  29     count(distinct c.id) as comments,
  30     sum(cl.score) as score
  31     from comment c
  32     left join comment_like cl on c.id = cl.comment_id
  33     group by c.creator_id
  34   ) cd on u.id = cd.creator_id;
  35
  36
  37 -- Add user aggregate triggers
  38
  39 -- initial user add
  40 create function user_aggregates_user()
  41 returns trigger language plpgsql
  42 as $$
  43 begin
  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;
  48   END IF;
  49   return null;
  50 end $$;
  51
  52 create trigger user_aggregates_user
  53 after insert or delete on user_
  54 for each row
  55 execute procedure user_aggregates_user();
  56
  57 -- post count
  58 create function user_aggregates_post_count()
  59 returns trigger language plpgsql
  60 as $$
  61 begin
  62   IF (TG_OP = 'INSERT') THEN
  63     update user_aggregates
  64     set post_count = post_count + 1 where user_id = NEW.creator_id;
  65
  66   ELSIF (TG_OP = 'DELETE') THEN
  67     update user_aggregates
  68     set post_count = post_count - 1 where user_id = OLD.creator_id;
  69
  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
  74     from (
  75       select u.id,
  76       coalesce(0, sum(pl.score)) as score
  77       -- User join because posts could be empty
  78       from user_ u
  79       left join post p on u.id = p.creator_id
  80       left join post_like pl on p.id = pl.post_id
  81       group by u.id
  82     ) pd
  83     where ua.user_id = pd.id;
  84
  85   END IF;
  86   return null;
  87 end $$;
  88
  89 create trigger user_aggregates_post_count
  90 after insert or delete on post
  91 for each row
  92 execute procedure user_aggregates_post_count();
  93
  94 -- post score
  95 create function user_aggregates_post_score()
  96 returns trigger language plpgsql
  97 as $$
  98 begin
  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
 104     from post p
 105     where ua.user_id = p.creator_id and p.id = NEW.post_id;
 106
 107   ELSIF (TG_OP = 'DELETE') THEN
 108     update user_aggregates ua
 109     set post_score = post_score - OLD.score
 110     from post p
 111     where ua.user_id = p.creator_id and p.id = OLD.post_id;
 112   END IF;
 113   return null;
 114 end $$;
 115
 116 create trigger user_aggregates_post_score
 117 after insert or delete on post_like
 118 for each row
 119 execute procedure user_aggregates_post_score();
 120
 121 -- comment count
 122 create function user_aggregates_comment_count()
 123 returns trigger language plpgsql
 124 as $$
 125 begin
 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;
 132
 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
 137     from (
 138       select u.id,
 139       coalesce(0, sum(cl.score)) as score
 140       -- User join because comments could be empty
 141       from user_ u
 142       left join comment c on u.id = c.creator_id
 143       left join comment_like cl on c.id = cl.comment_id
 144       group by u.id
 145     ) cd
 146     where ua.user_id = cd.id;
 147   END IF;
 148   return null;
 149 end $$;
 150
 151 create trigger user_aggregates_comment_count
 152 after insert or delete on comment
 153 for each row
 154 execute procedure user_aggregates_comment_count();
 155
 156 -- comment score
 157 create function user_aggregates_comment_score()
 158 returns trigger language plpgsql
 159 as $$
 160 begin
 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
 165     from comment c
 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
 170     from comment c
 171     where ua.user_id = c.creator_id and c.id = OLD.comment_id;
 172   END IF;
 173   return null;
 174 end $$;
 175
 176 create trigger user_aggregates_comment_score
 177 after insert or delete on comment_like
 178 for each row
 179 execute procedure user_aggregates_comment_score();