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 = OLD.creator_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     -- Need to get the post creator, not the voter
 101     update user_aggregates ua
 102     set post_score = post_score + NEW.score
 103     from post p
 104     where ua.user_id = p.creator_id and p.id = NEW.post_id;
 105
 106   ELSIF (TG_OP = 'DELETE') THEN
 107     update user_aggregates ua
 108     set post_score = post_score - OLD.score
 109     from post p
 110     where ua.user_id = p.creator_id and p.id = OLD.post_id;
 111   END IF;
 112   return null;
 113 end $$;
 114
 115 create trigger user_aggregates_post_score
 116 after insert or delete on post_like
 117 for each row
 118 execute procedure user_aggregates_post_score();
 119
 120 -- comment count
 121 create function user_aggregates_comment_count()
 122 returns trigger language plpgsql
 123 as $$
 124 begin
 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;
 131
 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
 136     from (
 137       select u.id,
 138       coalesce(0, sum(cl.score)) as score
 139       -- User join because comments could be empty
 140       from user_ u
 141       left join comment c on u.id = c.creator_id
 142       left join comment_like cl on c.id = cl.comment_id
 143       group by u.id
 144     ) cd
 145     where ua.user_id = OLD.creator_id;
 146   END IF;
 147   return null;
 148 end $$;
 149
 150 create trigger user_aggregates_comment_count
 151 after insert or delete on comment
 152 for each row
 153 execute procedure user_aggregates_comment_count();
 154
 155 -- comment score
 156 create function user_aggregates_comment_score()
 157 returns trigger language plpgsql
 158 as $$
 159 begin
 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
 164     from comment c
 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
 169     from comment c
 170     where ua.user_id = c.creator_id and c.id = OLD.comment_id;
 171   END IF;
 172   return null;
 173 end $$;
 174
 175 create trigger user_aggregates_comment_score
 176 after insert or delete on comment_like
 177 for each row
 178 execute procedure user_aggregates_comment_score();