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 execute procedure community_aggregates_post_count();
  54
  55 -- comment count
  56 create function community_aggregates_comment_count()
  57 returns trigger language plpgsql
  58 as $$
  59 begin
  60   IF (TG_OP = 'INSERT') THEN
  61     update community_aggregates
  62     set comments = comments + 1 from comment c join post p on p.id = c.post_id and p.id = NEW.post_id;
  63   ELSIF (TG_OP = 'DELETE') THEN
  64     update community_aggregates
  65     set comments = comments - 1 from comment c join post p on p.id = c.post_id and p.id = OLD.post_id;
  66   END IF;
  67   return null;
  68 end $$;
  69
  70 create trigger community_aggregates_comment_count
  71 after insert or delete on comment
  72 execute procedure community_aggregates_comment_count();
  73
  74 -- subscriber count
  75 create function community_aggregates_subscriber_count()
  76 returns trigger language plpgsql
  77 as $$
  78 begin
  79   IF (TG_OP = 'INSERT') THEN
  80     update community_aggregates
  81     set subscribers = subscribers + 1 where community_id = NEW.community_id;
  82   ELSIF (TG_OP = 'DELETE') THEN
  83     update community_aggregates
  84     set subscribers = subscribers - 1 where community_id = OLD.community_id;
  85   END IF;
  86   return null;
  87 end $$;
  88
  89 create trigger community_aggregates_subscriber_count
  90 after insert or delete on community_follower
  91 execute procedure community_aggregates_subscriber_count();
  92