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 default 0,
   6   posts bigint not null default 0,
   7   comments bigint not null default 0,
   8   published timestamp not null default now(),
   9   unique (community_id)
  10 );
  11
  12 insert into community_aggregates (community_id, subscribers, posts, comments, published)
  13   select
  14     c.id,
  15     coalesce(cf.subs, 0) as subscribers,
  16     coalesce(cd.posts, 0) as posts,
  17     coalesce(cd.comments, 0) as comments,
  18     c.published
  19   from community c
  20   left join (
  21     select
  22       p.community_id,
  23       count(distinct p.id) as posts,
  24       count(distinct ct.id) as comments
  25     from post p
  26     left join comment ct on p.id = ct.post_id
  27     group by p.community_id
  28   ) cd on cd.community_id = c.id
  29   left join (
  30     select
  31       community_follower.community_id,
  32       count(*) as subs
  33     from community_follower
  34     group by community_follower.community_id
  35   ) cf on cf.community_id = c.id;
  36
  37 -- Add community aggregate triggers
  38
  39 -- initial community add
  40 create function community_aggregates_community()
  41 returns trigger language plpgsql
  42 as $$
  43 begin
  44   IF (TG_OP = 'INSERT') THEN
  45     insert into community_aggregates (community_id) values (NEW.id);
  46   ELSIF (TG_OP = 'DELETE') THEN
  47     delete from community_aggregates where community_id = OLD.id;
  48   END IF;
  49   return null;
  50 end $$;
  51
  52 create trigger community_aggregates_community
  53 after insert or delete on community
  54 for each row
  55 execute procedure community_aggregates_community();
  56 -- post count
  57 create function community_aggregates_post_count()
  58 returns trigger language plpgsql
  59 as $$
  60 begin
  61   IF (TG_OP = 'INSERT') THEN
  62     update community_aggregates
  63     set posts = posts + 1 where community_id = NEW.community_id;
  64   ELSIF (TG_OP = 'DELETE') THEN
  65     update community_aggregates
  66     set posts = posts - 1 where community_id = OLD.community_id;
  67
  68     -- Update the counts if the post got deleted
  69     update community_aggregates ca
  70     set posts = coalesce(cd.posts, 0),
  71     comments = coalesce(cd.comments, 0)
  72     from (
  73       select
  74       c.id,
  75       count(distinct p.id) as posts,
  76       count(distinct ct.id) as comments
  77       from community c
  78       left join post p on c.id = p.community_id
  79       left join comment ct on p.id = ct.post_id
  80       group by c.id
  81     ) cd
  82     where ca.community_id = OLD.community_id;
  83   END IF;
  84   return null;
  85 end $$;
  86
  87 create trigger community_aggregates_post_count
  88 after insert or delete on post
  89 for each row
  90 execute procedure community_aggregates_post_count();
  91
  92 -- comment count
  93 create function community_aggregates_comment_count()
  94 returns trigger language plpgsql
  95 as $$
  96 begin
  97   IF (TG_OP = 'INSERT') THEN
  98     update community_aggregates ca
  99     set comments = comments + 1 from comment c, post p
 100     where p.id = c.post_id
 101     and p.id = NEW.post_id
 102     and ca.community_id = p.community_id;
 103   ELSIF (TG_OP = 'DELETE') THEN
 104     update community_aggregates ca
 105     set comments = comments - 1 from comment c, post p
 106     where p.id = c.post_id
 107     and p.id = OLD.post_id
 108     and ca.community_id = p.community_id;
 109
 110   END IF;
 111   return null;
 112 end $$;
 113
 114 create trigger community_aggregates_comment_count
 115 after insert or delete on comment
 116 for each row
 117 execute procedure community_aggregates_comment_count();
 118
 119 -- subscriber count
 120 create function community_aggregates_subscriber_count()
 121 returns trigger language plpgsql
 122 as $$
 123 begin
 124   IF (TG_OP = 'INSERT') THEN
 125     update community_aggregates
 126     set subscribers = subscribers + 1 where community_id = NEW.community_id;
 127   ELSIF (TG_OP = 'DELETE') THEN
 128     update community_aggregates
 129     set subscribers = subscribers - 1 where community_id = OLD.community_id;
 130   END IF;
 131   return null;
 132 end $$;
 133
 134 create trigger community_aggregates_subscriber_count
 135 after insert or delete on community_follower
 136 for each row
 137 execute procedure community_aggregates_subscriber_count();
 138