migrations/2020-12-04-183345_create_community_aggregates/up.sql

   1 -- Add community aggregates
   2 create table community_aggregates (
   3   id serial primary key,
   4   community_id int references community on update cascade on delete cascade not null,
   5   subscribers bigint not null,
   6   posts bigint not null,
   7   comments bigint not null,
   8   unique (community_id)
   9 );
  10
  11 insert into community_aggregates (community_id, subscribers, posts, comments)
  12   select
  13     c.id,
  14     coalesce(cf.subs, 0::bigint) as subscribers,
  15     coalesce(cd.posts, 0::bigint) as posts,
  16     coalesce(cd.comments, 0::bigint) as comments
  17   from community c
  18   left join (
  19     select
  20       p.community_id,
  21       count(distinct p.id) as posts,
  22       count(distinct ct.id) as comments
  23     from post p
  24     left join comment ct on p.id = ct.post_id
  25     group by p.community_id
  26   ) cd on cd.community_id = c.id
  27   left join (
  28     select
  29       community_follower.community_id,
  30       count(*) as subs
  31     from community_follower
  32     group by community_follower.community_id
  33   ) cf on cf.community_id = c.id;
  34
  35 -- Add community aggregate triggers
  36 -- post count
  37 create function community_aggregates_post_count()
  38 returns trigger language plpgsql
  39 as $$
  40 begin
  41   IF (TG_OP = 'INSERT') THEN
  42     update community_aggregates
  43     set posts = posts + 1 where community_id = NEW.community_id;
  44   ELSIF (TG_OP = 'DELETE') THEN
  45     update community_aggregates
  46     set posts = posts - 1 where community_id = OLD.community_id;
  47   END IF;
  48   return null;
  49 end $$;
  50
  51 create trigger community_aggregates_post_count
  52 after insert or delete on post
  53 for each row
  54 execute procedure community_aggregates_post_count();
  55
  56 -- comment count
  57 create function community_aggregates_comment_count()
  58 returns trigger language plpgsql
  59 as $$
  60 begin
  61   IF (TG_OP = 'INSERT') THEN
  62     update community_aggregates
  63     set comments = comments + 1 from comment c join post p on p.id = c.post_id and p.id = NEW.post_id;
  64   ELSIF (TG_OP = 'DELETE') THEN
  65     update community_aggregates
  66     set comments = comments - 1 from comment c join post p on p.id = c.post_id and p.id = OLD.post_id;
  67   END IF;
  68   return null;
  69 end $$;
  70
  71 create trigger community_aggregates_comment_count
  72 after insert or delete on comment
  73 for each row
  74 execute procedure community_aggregates_comment_count();
  75
  76 -- subscriber count
  77 create function community_aggregates_subscriber_count()
  78 returns trigger language plpgsql
  79 as $$
  80 begin
  81   IF (TG_OP = 'INSERT') THEN
  82     update community_aggregates
  83     set subscribers = subscribers + 1 where community_id = NEW.community_id;
  84   ELSIF (TG_OP = 'DELETE') THEN
  85     update community_aggregates
  86     set subscribers = subscribers - 1 where community_id = OLD.community_id;
  87   END IF;
  88   return null;
  89 end $$;
  90
  91 create trigger community_aggregates_subscriber_count
  92 after insert or delete on community_follower
  93 for each row
  94 execute procedure community_aggregates_subscriber_count();
  95