migrations/2020-12-03-035643_create_user_aggregates/up.sql

   1 -- Add user aggregates
   2 CREATE TABLE user_aggregates (
   3     id serial PRIMARY KEY,
   4     user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
   5     post_count bigint NOT NULL DEFAULT 0,
   6     post_score bigint NOT NULL DEFAULT 0,
   7     comment_count bigint NOT NULL DEFAULT 0,
   8     comment_score bigint NOT NULL DEFAULT 0,
   9     UNIQUE (user_id)
  10 );
  11
  12 INSERT INTO user_aggregates (user_id, post_count, post_score, comment_count, comment_score)
  13 SELECT
  14     u.id,
  15     coalesce(pd.posts, 0),
  16     coalesce(pd.score, 0),
  17     coalesce(cd.comments, 0),
  18     coalesce(cd.score, 0)
  19 FROM
  20     user_ u
  21     LEFT JOIN (
  22         SELECT
  23             p.creator_id,
  24             count(DISTINCT p.id) AS posts,
  25             sum(pl.score) AS score
  26         FROM
  27             post p
  28             LEFT JOIN post_like pl ON p.id = pl.post_id
  29         GROUP BY
  30             p.creator_id) pd ON u.id = pd.creator_id
  31     LEFT JOIN (
  32         SELECT
  33             c.creator_id,
  34             count(DISTINCT c.id) AS comments,
  35             sum(cl.score) AS score
  36         FROM
  37             comment c
  38             LEFT JOIN comment_like cl ON c.id = cl.comment_id
  39         GROUP BY
  40             c.creator_id) cd ON u.id = cd.creator_id;
  41
  42 -- Add user aggregate triggers
  43 -- initial user add
  44 CREATE FUNCTION user_aggregates_user ()
  45     RETURNS TRIGGER
  46     LANGUAGE plpgsql
  47     AS $$
  48 BEGIN
  49     IF (TG_OP = 'INSERT') THEN
  50         INSERT INTO user_aggregates (user_id)
  51             VALUES (NEW.id);
  52     ELSIF (TG_OP = 'DELETE') THEN
  53         DELETE FROM user_aggregates
  54         WHERE user_id = OLD.id;
  55     END IF;
  56     RETURN NULL;
  57 END
  58 $$;
  59
  60 CREATE TRIGGER user_aggregates_user
  61     AFTER INSERT OR DELETE ON user_
  62     FOR EACH ROW
  63     EXECUTE PROCEDURE user_aggregates_user ();
  64
  65 -- post count
  66 CREATE FUNCTION user_aggregates_post_count ()
  67     RETURNS TRIGGER
  68     LANGUAGE plpgsql
  69     AS $$
  70 BEGIN
  71     IF (TG_OP = 'INSERT') THEN
  72         UPDATE
  73             user_aggregates
  74         SET
  75             post_count = post_count + 1
  76         WHERE
  77             user_id = NEW.creator_id;
  78     ELSIF (TG_OP = 'DELETE') THEN
  79         UPDATE
  80             user_aggregates
  81         SET
  82             post_count = post_count - 1
  83         WHERE
  84             user_id = OLD.creator_id;
  85         -- If the post gets deleted, the score calculation trigger won't fire,
  86         -- so you need to re-calculate
  87         UPDATE
  88             user_aggregates ua
  89         SET
  90             post_score = pd.score
  91         FROM (
  92             SELECT
  93                 u.id,
  94                 coalesce(0, sum(pl.score)) AS score
  95                 -- User join because posts could be empty
  96             FROM
  97                 user_ u
  98             LEFT JOIN post p ON u.id = p.creator_id
  99             LEFT JOIN post_like pl ON p.id = pl.post_id
 100         GROUP BY
 101             u.id) pd
 102     WHERE
 103         ua.user_id = OLD.creator_id;
 104     END IF;
 105     RETURN NULL;
 106 END
 107 $$;
 108
 109 CREATE TRIGGER user_aggregates_post_count
 110     AFTER INSERT OR DELETE ON post
 111     FOR EACH ROW
 112     EXECUTE PROCEDURE user_aggregates_post_count ();
 113
 114 -- post score
 115 CREATE FUNCTION user_aggregates_post_score ()
 116     RETURNS TRIGGER
 117     LANGUAGE plpgsql
 118     AS $$
 119 BEGIN
 120     IF (TG_OP = 'INSERT') THEN
 121         -- Need to get the post creator, not the voter
 122         UPDATE
 123             user_aggregates ua
 124         SET
 125             post_score = post_score + NEW.score
 126         FROM
 127             post p
 128         WHERE
 129             ua.user_id = p.creator_id
 130             AND p.id = NEW.post_id;
 131     ELSIF (TG_OP = 'DELETE') THEN
 132         UPDATE
 133             user_aggregates ua
 134         SET
 135             post_score = post_score - OLD.score
 136         FROM
 137             post p
 138         WHERE
 139             ua.user_id = p.creator_id
 140             AND p.id = OLD.post_id;
 141     END IF;
 142     RETURN NULL;
 143 END
 144 $$;
 145
 146 CREATE TRIGGER user_aggregates_post_score
 147     AFTER INSERT OR DELETE ON post_like
 148     FOR EACH ROW
 149     EXECUTE PROCEDURE user_aggregates_post_score ();
 150
 151 -- comment count
 152 CREATE FUNCTION user_aggregates_comment_count ()
 153     RETURNS TRIGGER
 154     LANGUAGE plpgsql
 155     AS $$
 156 BEGIN
 157     IF (TG_OP = 'INSERT') THEN
 158         UPDATE
 159             user_aggregates
 160         SET
 161             comment_count = comment_count + 1
 162         WHERE
 163             user_id = NEW.creator_id;
 164     ELSIF (TG_OP = 'DELETE') THEN
 165         UPDATE
 166             user_aggregates
 167         SET
 168             comment_count = comment_count - 1
 169         WHERE
 170             user_id = OLD.creator_id;
 171         -- If the comment gets deleted, the score calculation trigger won't fire,
 172         -- so you need to re-calculate
 173         UPDATE
 174             user_aggregates ua
 175         SET
 176             comment_score = cd.score
 177         FROM (
 178             SELECT
 179                 u.id,
 180                 coalesce(0, sum(cl.score)) AS score
 181                 -- User join because comments could be empty
 182             FROM
 183                 user_ u
 184             LEFT JOIN comment c ON u.id = c.creator_id
 185             LEFT JOIN comment_like cl ON c.id = cl.comment_id
 186         GROUP BY
 187             u.id) cd
 188     WHERE
 189         ua.user_id = OLD.creator_id;
 190     END IF;
 191     RETURN NULL;
 192 END
 193 $$;
 194
 195 CREATE TRIGGER user_aggregates_comment_count
 196     AFTER INSERT OR DELETE ON comment
 197     FOR EACH ROW
 198     EXECUTE PROCEDURE user_aggregates_comment_count ();
 199
 200 -- comment score
 201 CREATE FUNCTION user_aggregates_comment_score ()
 202     RETURNS TRIGGER
 203     LANGUAGE plpgsql
 204     AS $$
 205 BEGIN
 206     IF (TG_OP = 'INSERT') THEN
 207         -- Need to get the post creator, not the voter
 208         UPDATE
 209             user_aggregates ua
 210         SET
 211             comment_score = comment_score + NEW.score
 212         FROM
 213             comment c
 214         WHERE
 215             ua.user_id = c.creator_id
 216             AND c.id = NEW.comment_id;
 217     ELSIF (TG_OP = 'DELETE') THEN
 218         UPDATE
 219             user_aggregates ua
 220         SET
 221             comment_score = comment_score - OLD.score
 222         FROM
 223             comment c
 224         WHERE
 225             ua.user_id = c.creator_id
 226             AND c.id = OLD.comment_id;
 227     END IF;
 228     RETURN NULL;
 229 END
 230 $$;
 231
 232 CREATE TRIGGER user_aggregates_comment_score
 233     AFTER INSERT OR DELETE ON comment_like
 234     FOR EACH ROW
 235     EXECUTE PROCEDURE user_aggregates_comment_score ();
 236