migrations/2021-08-02-002342_comment_count_fixes/up.sql

   1 -- Creating a new trigger for when comment.deleted is updated
   2 CREATE OR REPLACE FUNCTION post_aggregates_comment_deleted ()
   3     RETURNS TRIGGER
   4     LANGUAGE plpgsql
   5     AS $$
   6 BEGIN
   7     IF NEW.deleted = TRUE THEN
   8         UPDATE
   9             post_aggregates pa
  10         SET
  11             comments = comments - 1
  12         WHERE
  13             pa.post_id = NEW.post_id;
  14     ELSE
  15         UPDATE
  16             post_aggregates pa
  17         SET
  18             comments = comments + 1
  19         WHERE
  20             pa.post_id = NEW.post_id;
  21     END IF;
  22     RETURN NULL;
  23 END
  24 $$;
  25
  26 CREATE TRIGGER post_aggregates_comment_set_deleted
  27     AFTER UPDATE OF deleted ON comment
  28     FOR EACH ROW
  29     EXECUTE PROCEDURE post_aggregates_comment_deleted ();
  30
  31 -- Fix issue with being able to necro-bump your own post
  32 CREATE OR REPLACE FUNCTION post_aggregates_comment_count ()
  33     RETURNS TRIGGER
  34     LANGUAGE plpgsql
  35     AS $$
  36 BEGIN
  37     IF (TG_OP = 'INSERT') THEN
  38         UPDATE
  39             post_aggregates pa
  40         SET
  41             comments = comments + 1,
  42             newest_comment_time = NEW.published
  43         WHERE
  44             pa.post_id = NEW.post_id;
  45         -- A 2 day necro-bump limit
  46         UPDATE
  47             post_aggregates pa
  48         SET
  49             newest_comment_time_necro = NEW.published
  50         FROM
  51             post p
  52         WHERE
  53             pa.post_id = p.id
  54             AND pa.post_id = NEW.post_id
  55             -- Fix issue with being able to necro-bump your own post
  56             AND NEW.creator_id != p.creator_id
  57             AND pa.published > ('now'::timestamp - '2 days'::interval);
  58     ELSIF (TG_OP = 'DELETE') THEN
  59         -- Join to post because that post may not exist anymore
  60         UPDATE
  61             post_aggregates pa
  62         SET
  63             comments = comments - 1
  64         FROM
  65             post p
  66         WHERE
  67             pa.post_id = p.id
  68             AND pa.post_id = OLD.post_id;
  69     ELSIF (TG_OP = 'UPDATE') THEN
  70         -- Join to post because that post may not exist anymore
  71         UPDATE
  72             post_aggregates pa
  73         SET
  74             comments = comments - 1
  75         FROM
  76             post p
  77         WHERE
  78             pa.post_id = p.id
  79             AND pa.post_id = OLD.post_id;
  80     END IF;
  81     RETURN NULL;
  82 END
  83 $$;
  84