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