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
  20     community c
  21     LEFT JOIN (
  22         SELECT
  23             p.community_id,
  24             count(DISTINCT p.id) AS posts,
  25             count(DISTINCT ct.id) AS comments
  26         FROM
  27             post p
  28             LEFT JOIN comment ct ON p.id = ct.post_id
  29         GROUP BY
  30             p.community_id) cd ON cd.community_id = c.id
  31     LEFT JOIN (
  32         SELECT
  33             community_follower.community_id,
  34             count(*) AS subs
  35         FROM
  36             community_follower
  37         GROUP BY
  38             community_follower.community_id) cf ON cf.community_id = c.id;
  39
  40 -- Add community aggregate triggers
  41 -- initial community add
  42 CREATE FUNCTION community_aggregates_community ()
  43     RETURNS TRIGGER
  44     LANGUAGE plpgsql
  45     AS $$
  46 BEGIN
  47     IF (TG_OP = 'INSERT') THEN
  48         INSERT INTO community_aggregates (community_id)
  49             VALUES (NEW.id);
  50     ELSIF (TG_OP = 'DELETE') THEN
  51         DELETE FROM community_aggregates
  52         WHERE community_id = OLD.id;
  53     END IF;
  54     RETURN NULL;
  55 END
  56 $$;
  57
  58 CREATE TRIGGER community_aggregates_community
  59     AFTER INSERT OR DELETE ON community
  60     FOR EACH ROW
  61     EXECUTE PROCEDURE community_aggregates_community ();
  62
  63 -- post count
  64 CREATE FUNCTION community_aggregates_post_count ()
  65     RETURNS TRIGGER
  66     LANGUAGE plpgsql
  67     AS $$
  68 BEGIN
  69     IF (TG_OP = 'INSERT') THEN
  70         UPDATE
  71             community_aggregates
  72         SET
  73             posts = posts + 1
  74         WHERE
  75             community_id = NEW.community_id;
  76     ELSIF (TG_OP = 'DELETE') THEN
  77         UPDATE
  78             community_aggregates
  79         SET
  80             posts = posts - 1
  81         WHERE
  82             community_id = OLD.community_id;
  83         -- Update the counts if the post got deleted
  84         UPDATE
  85             community_aggregates ca
  86         SET
  87             posts = coalesce(cd.posts, 0),
  88             comments = coalesce(cd.comments, 0)
  89         FROM (
  90             SELECT
  91                 c.id,
  92                 count(DISTINCT p.id) AS posts,
  93                 count(DISTINCT ct.id) AS comments
  94             FROM
  95                 community c
  96             LEFT JOIN post p ON c.id = p.community_id
  97             LEFT JOIN comment ct ON p.id = ct.post_id
  98         GROUP BY
  99             c.id) cd
 100     WHERE
 101         ca.community_id = OLD.community_id;
 102     END IF;
 103     RETURN NULL;
 104 END
 105 $$;
 106
 107 CREATE TRIGGER community_aggregates_post_count
 108     AFTER INSERT OR DELETE ON post
 109     FOR EACH ROW
 110     EXECUTE PROCEDURE community_aggregates_post_count ();
 111
 112 -- comment count
 113 CREATE FUNCTION community_aggregates_comment_count ()
 114     RETURNS TRIGGER
 115     LANGUAGE plpgsql
 116     AS $$
 117 BEGIN
 118     IF (TG_OP = 'INSERT') THEN
 119         UPDATE
 120             community_aggregates ca
 121         SET
 122             comments = comments + 1
 123         FROM
 124             comment c,
 125             post p
 126         WHERE
 127             p.id = c.post_id
 128             AND p.id = NEW.post_id
 129             AND ca.community_id = p.community_id;
 130     ELSIF (TG_OP = 'DELETE') THEN
 131         UPDATE
 132             community_aggregates ca
 133         SET
 134             comments = comments - 1
 135         FROM
 136             comment c,
 137             post p
 138         WHERE
 139             p.id = c.post_id
 140             AND p.id = OLD.post_id
 141             AND ca.community_id = p.community_id;
 142     END IF;
 143     RETURN NULL;
 144 END
 145 $$;
 146
 147 CREATE TRIGGER community_aggregates_comment_count
 148     AFTER INSERT OR DELETE ON comment
 149     FOR EACH ROW
 150     EXECUTE PROCEDURE community_aggregates_comment_count ();
 151
 152 -- subscriber count
 153 CREATE FUNCTION community_aggregates_subscriber_count ()
 154     RETURNS TRIGGER
 155     LANGUAGE plpgsql
 156     AS $$
 157 BEGIN
 158     IF (TG_OP = 'INSERT') THEN
 159         UPDATE
 160             community_aggregates
 161         SET
 162             subscribers = subscribers + 1
 163         WHERE
 164             community_id = NEW.community_id;
 165     ELSIF (TG_OP = 'DELETE') THEN
 166         UPDATE
 167             community_aggregates
 168         SET
 169             subscribers = subscribers - 1
 170         WHERE
 171             community_id = OLD.community_id;
 172     END IF;
 173     RETURN NULL;
 174 END
 175 $$;
 176
 177 CREATE TRIGGER community_aggregates_subscriber_count
 178     AFTER INSERT OR DELETE ON community_follower
 179     FOR EACH ROW
 180     EXECUTE PROCEDURE community_aggregates_subscriber_count ();
 181