migrations/2020-12-14-020038_create_comment_aggregates/up.sql

   1 -- Add comment aggregates
   2 CREATE TABLE comment_aggregates (
   3     id serial PRIMARY KEY,
   4     comment_id int REFERENCES COMMENT ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
   5     score bigint NOT NULL DEFAULT 0,
   6     upvotes bigint NOT NULL DEFAULT 0,
   7     downvotes bigint NOT NULL DEFAULT 0,
   8     published timestamp NOT NULL DEFAULT now(),
   9     UNIQUE (comment_id)
  10 );
  11
  12 INSERT INTO comment_aggregates (comment_id, score, upvotes, downvotes, published)
  13 SELECT
  14     c.id,
  15     COALESCE(cl.total, 0::bigint) AS score,
  16     COALESCE(cl.up, 0::bigint) AS upvotes,
  17     COALESCE(cl.down, 0::bigint) AS downvotes,
  18     c.published
  19 FROM
  20     comment c
  21     LEFT JOIN (
  22         SELECT
  23             l.comment_id AS id,
  24             sum(l.score) AS total,
  25             count(
  26                 CASE WHEN l.score = 1 THEN
  27                     1
  28                 ELSE
  29                     NULL::integer
  30                 END) AS up,
  31             count(
  32                 CASE WHEN l.score = '-1'::integer THEN
  33                     1
  34                 ELSE
  35                     NULL::integer
  36                 END) AS down
  37         FROM
  38             comment_like l
  39         GROUP BY
  40             l.comment_id) cl ON cl.id = c.id;
  41
  42 -- Add comment aggregate triggers
  43 -- initial comment add
  44 CREATE FUNCTION comment_aggregates_comment ()
  45     RETURNS TRIGGER
  46     LANGUAGE plpgsql
  47     AS $$
  48 BEGIN
  49     IF (TG_OP = 'INSERT') THEN
  50         INSERT INTO comment_aggregates (comment_id)
  51             VALUES (NEW.id);
  52     ELSIF (TG_OP = 'DELETE') THEN
  53         DELETE FROM comment_aggregates
  54         WHERE comment_id = OLD.id;
  55     END IF;
  56     RETURN NULL;
  57 END
  58 $$;
  59
  60 CREATE TRIGGER comment_aggregates_comment
  61     AFTER INSERT OR DELETE ON comment
  62     FOR EACH ROW
  63     EXECUTE PROCEDURE comment_aggregates_comment ();
  64
  65 -- comment score
  66 CREATE FUNCTION comment_aggregates_score ()
  67     RETURNS TRIGGER
  68     LANGUAGE plpgsql
  69     AS $$
  70 BEGIN
  71     IF (TG_OP = 'INSERT') THEN
  72         UPDATE
  73             comment_aggregates ca
  74         SET
  75             score = score + NEW.score,
  76             upvotes = CASE WHEN NEW.score = 1 THEN
  77                 upvotes + 1
  78             ELSE
  79                 upvotes
  80             END,
  81             downvotes = CASE WHEN NEW.score = - 1 THEN
  82                 downvotes + 1
  83             ELSE
  84                 downvotes
  85             END
  86         WHERE
  87             ca.comment_id = NEW.comment_id;
  88     ELSIF (TG_OP = 'DELETE') THEN
  89         -- Join to comment because that comment may not exist anymore
  90         UPDATE
  91             comment_aggregates ca
  92         SET
  93             score = score - OLD.score,
  94             upvotes = CASE WHEN OLD.score = 1 THEN
  95                 upvotes - 1
  96             ELSE
  97                 upvotes
  98             END,
  99             downvotes = CASE WHEN OLD.score = - 1 THEN
 100                 downvotes - 1
 101             ELSE
 102                 downvotes
 103             END
 104         FROM
 105             comment c
 106         WHERE
 107             ca.comment_id = c.id
 108             AND ca.comment_id = OLD.comment_id;
 109     END IF;
 110     RETURN NULL;
 111 END
 112 $$;
 113
 114 CREATE TRIGGER comment_aggregates_score
 115     AFTER INSERT OR DELETE ON comment_like
 116     FOR EACH ROW
 117     EXECUTE PROCEDURE comment_aggregates_score ();
 118