migrations/2019-10-19-052737_create_user_mention/up.sql

   1 CREATE TABLE user_mention (
   2     id serial PRIMARY KEY,
   3     recipient_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
   4     comment_id int REFERENCES COMMENT ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
   5     read boolean DEFAULT FALSE NOT NULL,
   6     published timestamp NOT NULL DEFAULT now(),
   7     UNIQUE (recipient_id, comment_id)
   8 );
   9
  10 CREATE VIEW user_mention_view AS
  11 SELECT
  12     c.id,
  13     um.id AS user_mention_id,
  14     c.creator_id,
  15     c.post_id,
  16     c.parent_id,
  17     c.content,
  18     c.removed,
  19     um.read,
  20     c.published,
  21     c.updated,
  22     c.deleted,
  23     c.community_id,
  24     c.banned,
  25     c.banned_from_community,
  26     c.creator_name,
  27     c.score,
  28     c.upvotes,
  29     c.downvotes,
  30     c.user_id,
  31     c.my_vote,
  32     c.saved,
  33     um.recipient_id
  34 FROM
  35     user_mention um,
  36     comment_view c
  37 WHERE
  38     um.comment_id = c.id;
  39