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