migrations/2020-12-10-152350_create_post_aggregates/up.sql

   1 -- Add post aggregates
   2 create table post_aggregates (
   3   id serial primary key,
   4   post_id int references post on update cascade on delete cascade not null,
   5   comments bigint not null default 0,
   6   score bigint not null default 0,
   7   upvotes bigint not null default 0,
   8   downvotes bigint not null default 0,
   9   newest_comment_time timestamp not null default now(),
  10   unique (post_id)
  11 );
  12
  13 insert into post_aggregates (post_id, comments, score, upvotes, downvotes, newest_comment_time)
  14   select
  15     p.id,
  16     coalesce(ct.comments, 0::bigint) as comments,
  17     coalesce(pl.score, 0::bigint) as score,
  18     coalesce(pl.upvotes, 0::bigint) as upvotes,
  19     coalesce(pl.downvotes, 0::bigint) as downvotes,
  20     greatest(ct.recent_comment_time, p.published) as newest_activity_time
  21   from post p
  22   left join (
  23     select comment.post_id,
  24     count(*) as comments,
  25     max(comment.published) as recent_comment_time
  26     from comment
  27     group by comment.post_id
  28   ) ct on ct.post_id = p.id
  29   left join (
  30     select post_like.post_id,
  31     sum(post_like.score) as score,
  32     sum(post_like.score) filter (where post_like.score = 1) as upvotes,
  33     -sum(post_like.score) filter (where post_like.score = '-1'::integer) as downvotes
  34     from post_like
  35     group by post_like.post_id
  36   ) pl on pl.post_id = p.id;
  37
  38 -- Add community aggregate triggers
  39
  40 -- initial post add
  41 create function post_aggregates_post()
  42 returns trigger language plpgsql
  43 as $$
  44 begin
  45   IF (TG_OP = 'INSERT') THEN
  46     insert into post_aggregates (post_id) values (NEW.id);
  47   ELSIF (TG_OP = 'DELETE') THEN
  48     delete from post_aggregates where post_id = OLD.id;
  49   END IF;
  50   return null;
  51 end $$;
  52
  53 create trigger post_aggregates_post
  54 after insert or delete on post
  55 for each row
  56 execute procedure post_aggregates_post();
  57
  58 -- comment count
  59 create function post_aggregates_comment_count()
  60 returns trigger language plpgsql
  61 as $$
  62 begin
  63   IF (TG_OP = 'INSERT') THEN
  64     update post_aggregates pa
  65     set comments = comments + 1,
  66     newest_comment_time = NEW.published
  67     where pa.post_id = NEW.post_id;
  68   ELSIF (TG_OP = 'DELETE') THEN
  69     update post_aggregates pa
  70     set comments = comments - 1
  71     where pa.post_id = OLD.post_id;
  72   END IF;
  73   return null;
  74 end $$;
  75
  76 create trigger post_aggregates_comment_count
  77 after insert or delete on comment
  78 for each row
  79 execute procedure post_aggregates_comment_count();
  80
  81 -- post score
  82 create function post_aggregates_score()
  83 returns trigger language plpgsql
  84 as $$
  85 begin
  86   IF (TG_OP = 'INSERT') THEN
  87     update post_aggregates pa
  88     set score = score + NEW.score,
  89     upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
  90     downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end
  91     where pa.post_id = NEW.post_id;
  92
  93   ELSIF (TG_OP = 'DELETE') THEN
  94     update post_aggregates pa
  95     set score = score - OLD.score,
  96     upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
  97     downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end
  98     where pa.post_id = OLD.post_id;
  99
 100   END IF;
 101   return null;
 102 end $$;
 103
 104 create trigger post_aggregates_score
 105 after insert or delete on post_like
 106 for each row
 107 execute procedure post_aggregates_score();