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