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,
   6   post_score bigint not null,
   7   comment_count bigint not null,
   8   comment_score bigint not null,
   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 -- post count
  39 create function user_aggregates_post_count()
  40 returns trigger language plpgsql
  41 as $$
  42 begin
  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;
  49   END IF;
  50   return null;
  51 end $$;
  52
  53 create trigger user_aggregates_post_count
  54 after insert or delete on post
  55 for each row
  56 execute procedure user_aggregates_post_count();
  57
  58 -- post score
  59 create function user_aggregates_post_score()
  60 returns trigger language plpgsql
  61 as $$
  62 begin
  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;
  75   END IF;
  76   return null;
  77 end $$;
  78
  79 create trigger user_aggregates_post_score
  80 after insert or delete on post_like
  81 for each row
  82 execute procedure user_aggregates_post_score();
  83
  84 -- comment count
  85 create function user_aggregates_comment_count()
  86 returns trigger language plpgsql
  87 as $$
  88 begin
  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;
  95   END IF;
  96   return null;
  97 end $$;
  98
  99 create trigger user_aggregates_comment_count
 100 after insert or delete on comment
 101 for each row
 102 execute procedure user_aggregates_comment_count();
 103
 104 -- comment score
 105 create function user_aggregates_comment_score()
 106 returns trigger language plpgsql
 107 as $$
 108 begin
 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;
 120   END IF;
 121   return null;
 122 end $$;
 123
 124 create trigger user_aggregates_comment_score
 125 after insert or delete on comment_like
 126 execute procedure user_aggregates_comment_score();