migrations/2020-12-14-020038_create_comment_aggregates/up.sql

   1 -- Add comment aggregates
   2 create table comment_aggregates (
   3   id serial primary key,
   4   comment_id int references comment on update cascade on delete cascade not null,
   5   score bigint not null default 0,
   6   upvotes bigint not null default 0,
   7   downvotes bigint not null default 0,
   8   published timestamp not null default now(),
   9   unique (comment_id)
  10 );
  11
  12 insert into comment_aggregates (comment_id, score, upvotes, downvotes, published)
  13   select
  14     c.id,
  15     COALESCE(cl.total, 0::bigint) AS score,
  16     COALESCE(cl.up, 0::bigint) AS upvotes,
  17     COALESCE(cl.down, 0::bigint) AS downvotes,
  18     c.published
  19   from comment c
  20   left join ( select l.comment_id as id,
  21     sum(l.score) as total,
  22     count(
  23       case
  24       when l.score = 1 then 1
  25       else null::integer
  26       end) as up,
  27     count(
  28       case
  29       when l.score = '-1'::integer then 1
  30       else null::integer
  31       end) as down
  32     from comment_like l
  33     group by l.comment_id) cl on cl.id = c.id;
  34
  35 -- Add comment aggregate triggers
  36
  37 -- initial comment add
  38 create function comment_aggregates_comment()
  39 returns trigger language plpgsql
  40 as $$
  41 begin
  42   IF (TG_OP = 'INSERT') THEN
  43     insert into comment_aggregates (comment_id) values (NEW.id);
  44   ELSIF (TG_OP = 'DELETE') THEN
  45     delete from comment_aggregates where comment_id = OLD.id;
  46   END IF;
  47   return null;
  48 end $$;
  49
  50 create trigger comment_aggregates_comment
  51 after insert or delete on comment
  52 for each row
  53 execute procedure comment_aggregates_comment();
  54
  55 -- comment score
  56 create function comment_aggregates_score()
  57 returns trigger language plpgsql
  58 as $$
  59 begin
  60   IF (TG_OP = 'INSERT') THEN
  61     update comment_aggregates ca
  62     set score = score + NEW.score,
  63     upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
  64     downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end
  65     where ca.comment_id = NEW.comment_id;
  66
  67   ELSIF (TG_OP = 'DELETE') THEN
  68     -- Join to comment because that comment may not exist anymore
  69     update comment_aggregates ca
  70     set score = score - OLD.score,
  71     upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
  72     downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end
  73     from comment c
  74     where ca.comment_id = c.id
  75     and ca.comment_id = OLD.comment_id;
  76
  77   END IF;
  78   return null;
  79 end $$;
  80
  81 create trigger comment_aggregates_score
  82 after insert or delete on comment_like
  83 for each row
  84 execute procedure comment_aggregates_score();