Adding username mentions / tagging from comments.

- Fixes #293

21 files changed, 1151 insertions, 57 deletions

diff --git a/server/migrations/2019-10-19-052737_create_user_mention/down.sql b/server/migrations/2019-10-19-052737_create_user_mention/down.sql
new file mode 100644
index 00000000..7165bc86
--- /dev/null
+++ b/server/migrations/2019-10-19-052737_create_user_mention/down.sql
@@ -0,0 +1,2 @@
+drop view user_mention_view;
+drop table user_mention;
diff --git a/server/migrations/2019-10-19-052737_create_user_mention/up.sql b/server/migrations/2019-10-19-052737_create_user_mention/up.sql
new file mode 100644
index 00000000..81fef008
--- /dev/null
+++ b/server/migrations/2019-10-19-052737_create_user_mention/up.sql
@@ -0,0 +1,35 @@
+create table user_mention (
+  id serial primary key,
+  recipient_id int references user_ on update cascade on delete cascade not null,
+  comment_id int references comment on update cascade on delete cascade not null,
+  read boolean default false not null,
+  published timestamp not null default now(),
+  unique(recipient_id, comment_id)
+);
+
+create view user_mention_view as
+select 
+    c.id,
+    um.id as user_mention_id,
+    c.creator_id,
+    c.post_id,
+    c.parent_id,
+    c.content,
+    c.removed,
+    um.read,
+    c.published,
+    c.updated,
+    c.deleted,
+    c.community_id,
+    c.banned,
+    c.banned_from_community,
+    c.creator_name,
+    c.score,
+    c.upvotes,
+    c.downvotes,
+    c.user_id,
+    c.my_vote,
+    c.saved,
+    um.recipient_id
+from user_mention um, comment_view c
+where um.comment_id = c.id;
diff --git a/server/src/api/comment.rs b/server/src/api/comment.rs
index ec010d2f..a5ccd358 100644
--- a/server/src/api/comment.rs
+++ b/server/src/api/comment.rs
@@ -85,6 +85,35 @@ impl Perform<CommentResponse> for Oper<CreateComment> {
       Err(_e) => return Err(APIError::err(&self.op, "couldnt_create_comment"))?,
     };
 
+    // Scan the comment for user mentions, add those rows
+    let extracted_usernames = extract_usernames(&comment_form.content);
+
+    for username_mention in &extracted_usernames {
+      let mention_user = User_::read_from_name(&conn, username_mention.to_string());
+
+      if mention_user.is_ok() {
+        let mention_user_id = mention_user?.id;
+
+        // You can't mention yourself
+        // At some point, make it so you can't tag the parent creator either
+        // This can cause two notifications, one for reply and the other for mention
+        if mention_user_id != user_id {
+          let user_mention_form = UserMentionForm {
+            recipient_id: mention_user_id,
+            comment_id: inserted_comment.id,
+            read: None,
+          };
+
+          // Allow this to fail softly, since comment edits might re-update or replace it
+          // Let the uniqueness handle this fail
+          match UserMention::create(&conn, &user_mention_form) {
+            Ok(_mention) => (),
+            Err(_e) => eprintln!("{}", &_e),
+          }
+        }
+      }
+    }
+
     // You like your own comment by default
     let like_form = CommentLikeForm {
       comment_id: inserted_comment.id,
@@ -170,6 +199,35 @@ impl Perform<CommentResponse> for Oper<EditComment> {
       Err(_e) => return Err(APIError::err(&self.op, "couldnt_update_comment"))?,
     };
 
+    // Scan the comment for user mentions, add those rows
+    let extracted_usernames = extract_usernames(&comment_form.content);
+
+    for username_mention in &extracted_usernames {
+      let mention_user = User_::read_from_name(&conn, username_mention.to_string());
+
+      if mention_user.is_ok() {
+        let mention_user_id = mention_user?.id;
+
+        // You can't mention yourself
+        // At some point, make it so you can't tag the parent creator either
+        // This can cause two notifications, one for reply and the other for mention
+        if mention_user_id != user_id {
+          let user_mention_form = UserMentionForm {
+            recipient_id: mention_user_id,
+            comment_id: data.edit_id,
+            read: None,
+          };
+
+          // Allow this to fail softly, since comment edits might re-update or replace it
+          // Let the uniqueness handle this fail
+          match UserMention::create(&conn, &user_mention_form) {
+            Ok(_mention) => (),
+            Err(_e) => eprintln!("{}", &_e),
+          }
+        }
+      }
+    }
+
     // Mod tables
     if let Some(removed) = data.removed.to_owned() {
       let form = ModRemoveCommentForm {
diff --git a/server/src/api/mod.rs b/server/src/api/mod.rs
index 5ffb57d8..cab8a77b 100644
--- a/server/src/api/mod.rs
+++ b/server/src/api/mod.rs
@@ -8,9 +8,11 @@ use crate::db::moderator_views::*;
 use crate::db::post::*;
 use crate::db::post_view::*;
 use crate::db::user::*;
+use crate::db::user_mention::*;
+use crate::db::user_mention_view::*;
 use crate::db::user_view::*;
 use crate::db::*;
-use crate::{has_slurs, naive_from_unix, naive_now, remove_slurs, Settings};
+use crate::{extract_usernames, has_slurs, naive_from_unix, naive_now, remove_slurs, Settings};
 use failure::Error;
 use serde::{Deserialize, Serialize};
 
@@ -43,6 +45,8 @@ pub enum UserOperation {
   GetFollowedCommunities,
   GetUserDetails,
   GetReplies,
+  GetUserMentions,
+  EditUserMention,
   GetModlog,
   BanFromCommunity,
   AddModToCommunity,
diff --git a/server/src/api/user.rs b/server/src/api/user.rs
index 2de80905..563ae0a2 100644
--- a/server/src/api/user.rs
+++ b/server/src/api/user.rs
@@ -61,6 +61,12 @@ pub struct GetRepliesResponse {
 }
 
 #[derive(Serialize, Deserialize)]
+pub struct GetUserMentionsResponse {
+  op: String,
+  mentions: Vec<UserMentionView>,
+}
+
+#[derive(Serialize, Deserialize)]
 pub struct MarkAllAsRead {
   auth: String,
 }
@@ -104,6 +110,28 @@ pub struct GetReplies {
 }
 
 #[derive(Serialize, Deserialize)]
+pub struct GetUserMentions {
+  sort: String,
+  page: Option<i64>,
+  limit: Option<i64>,
+  unread_only: bool,
+  auth: String,
+}
+
+#[derive(Serialize, Deserialize)]
+pub struct EditUserMention {
+  user_mention_id: i32,
+  read: Option<bool>,
+  auth: String,
+}
+
+#[derive(Serialize, Deserialize, Clone)]
+pub struct UserMentionResponse {
+  op: String,
+  mention: UserMentionView,
+}
+
+#[derive(Serialize, Deserialize)]
 pub struct DeleteAccount {
   password: String,
   auth: String,
@@ -299,7 +327,6 @@ impl Perform<GetUserDetailsResponse> for Oper<GetUserDetails> {
       None => false,
     };
 
-    //TODO add save
     let sort = SortType::from_str(&data.sort)?;
 
     let user_details_id = match data.user_id {
@@ -541,7 +568,6 @@ impl Perform<GetRepliesResponse> for Oper<GetReplies> {
       data.limit,
     )?;
 
-    // Return the jwt
     Ok(GetRepliesResponse {
       op: self.op.to_string(),
       replies: replies,
@@ -549,6 +575,71 @@ impl Perform<GetRepliesResponse> for Oper<GetReplies> {
   }
 }
 
+impl Perform<GetUserMentionsResponse> for Oper<GetUserMentions> {
+  fn perform(&self) -> Result<GetUserMentionsResponse, Error> {
+    let data: &GetUserMentions = &self.data;
+    let conn = establish_connection();
+
+    let claims = match Claims::decode(&data.auth) {
+      Ok(claims) => claims.claims,
+      Err(_e) => return Err(APIError::err(&self.op, "not_logged_in"))?,
+    };
+
+    let user_id = claims.id;
+
+    let sort = SortType::from_str(&data.sort)?;
+
+    let mentions = UserMentionView::get_mentions(
+      &conn,
+      user_id,
+      &sort,
+      data.unread_only,
+      data.page,
+      data.limit,
+    )?;
+
+    Ok(GetUserMentionsResponse {
+      op: self.op.to_string(),
+      mentions: mentions,
+    })
+  }
+}
+
+impl Perform<UserMentionResponse> for Oper<EditUserMention> {
+  fn perform(&self) -> Result<UserMentionResponse, Error> {
+    let data: &EditUserMention = &self.data;
+    let conn = establish_connection();
+
+    let claims = match Claims::decode(&data.auth) {
+      Ok(claims) => claims.claims,
+      Err(_e) => return Err(APIError::err(&self.op, "not_logged_in"))?,
+    };
+
+    let user_id = claims.id;
+
+    let user_mention = UserMention::read(&conn, data.user_mention_id)?;
+
+    let user_mention_form = UserMentionForm {
+      recipient_id: user_id,
+      comment_id: user_mention.comment_id,
+      read: data.read.to_owned(),
+    };
+
+    let _updated_user_mention =
+      match UserMention::update(&conn, user_mention.id, &user_mention_form) {
+        Ok(comment) => comment,
+        Err(_e) => return Err(APIError::err(&self.op, "couldnt_update_comment"))?,
+      };
+
+    let user_mention_view = UserMentionView::read(&conn, user_mention.id, user_id)?;
+
+    Ok(UserMentionResponse {
+      op: self.op.to_string(),
+      mention: user_mention_view,
+    })
+  }
+}
+
 impl Perform<GetRepliesResponse> for Oper<MarkAllAsRead> {
   fn perform(&self) -> Result<GetRepliesResponse, Error> {
     let data: &MarkAllAsRead = &self.data;
@@ -581,11 +672,27 @@ impl Perform<GetRepliesResponse> for Oper<MarkAllAsRead> {
       };
     }
 
-    let replies = ReplyView::get_replies(&conn, user_id, &SortType::New, true, Some(1), Some(999))?;
+    // Mentions
+    let mentions =
+      UserMentionView::get_mentions(&conn, user_id, &SortType::New, true, Some(1), Some(999))?;
+
+    for mention in &mentions {
+      let mention_form = UserMentionForm {
+        recipient_id: mention.to_owned().recipient_id,
+        comment_id: mention.to_owned().id,
+        read: Some(true),
+      };
+
+      let _updated_mention =
+        match UserMention::update(&conn, mention.user_mention_id, &mention_form) {
+          Ok(mention) => mention,
+          Err(_e) => return Err(APIError::err(&self.op, "couldnt_update_comment"))?,
+        };
+    }
 
     Ok(GetRepliesResponse {
       op: self.op.to_string(),
-      replies: replies,
+      replies: vec![],
     })
   }
 }
diff --git a/server/src/db/comment_view.rs b/server/src/db/comment_view.rs
index b192e6eb..88190464 100644
--- a/server/src/db/comment_view.rs
+++ b/server/src/db/comment_view.rs
@@ -69,7 +69,6 @@ impl CommentView {
 
     let (limit, offset) = limit_and_offset(page, limit);
 
-    // TODO no limits here?
     let mut query = comment_view.into_boxed();
 
     // The view lets you pass a null user_id, if you're not logged in
diff --git a/server/src/db/mod.rs b/server/src/db/mod.rs
index 51a59139..ac3c3ae3 100644
--- a/server/src/db/mod.rs
+++ b/server/src/db/mod.rs
@@ -14,6 +14,8 @@ pub mod moderator_views;
 pub mod post;
 pub mod post_view;
 pub mod user;
+pub mod user_mention;
+pub mod user_mention_view;
 pub mod user_view;
 
 pub trait Crud<T> {
diff --git a/server/src/db/src/schema.rs b/server/src/db/src/schema.rs
new file mode 100644
index 00000000..8693db25
--- /dev/null
+++ b/server/src/db/src/schema.rs
@@ -0,0 +1,345 @@
+table! {
+    category (id) {
+        id -> Int4,
+        name -> Varchar,
+    }
+}
+
+table! {
+    comment (id) {
+        id -> Int4,
+        creator_id -> Int4,
+        post_id -> Int4,
+        parent_id -> Nullable<Int4>,
+        content -> Text,
+        removed -> Bool,
+        read -> Bool,
+        published -> Timestamp,
+        updated -> Nullable<Timestamp>,
+        deleted -> Bool,
+    }
+}
+
+table! {
+    comment_like (id) {
+        id -> Int4,
+        user_id -> Int4,
+        comment_id -> Int4,
+        post_id -> Int4,
+        score -> Int2,
+        published -> Timestamp,
+    }
+}
+
+table! {
+    comment_saved (id) {
+        id -> Int4,
+        comment_id -> Int4,
+        user_id -> Int4,
+        published -> Timestamp,
+    }
+}
+
+table! {
+    community (id) {
+        id -> Int4,
+        name -> Varchar,
+        title -> Varchar,
+        description -> Nullable<Text>,
+        category_id -> Int4,
+        creator_id -> Int4,
+        removed -> Bool,
+        published -> Timestamp,
+        updated -> Nullable<Timestamp>,
+        deleted -> Bool,
+        nsfw -> Bool,
+    }
+}
+
+table! {
+    community_follower (id) {
+        id -> Int4,
+        community_id -> Int4,
+        user_id -> Int4,
+        published -> Timestamp,
+    }
+}
+
+table! {
+    community_moderator (id) {
+        id -> Int4,
+        community_id -> Int4,
+        user_id -> Int4,
+        published -> Timestamp,
+    }
+}
+
+table! {
+    community_user_ban (id) {
+        id -> Int4,
+        community_id -> Int4,
+        user_id -> Int4,
+        published -> Timestamp,
+    }
+}
+
+table! {
+    mod_add (id) {
+        id -> Int4,
+        mod_user_id -> Int4,
+        other_user_id -> Int4,
+        removed -> Nullable<Bool>,
+        when_ -> Timestamp,
+    }
+}
+
+table! {
+    mod_add_community (id) {
+        id -> Int4,
+        mod_user_id -> Int4,
+        other_user_id -> Int4,
+        community_id -> Int4,
+        removed -> Nullable<Bool>,
+        when_ -> Timestamp,
+    }
+}
+
+table! {
+    mod_ban (id) {
+        id -> Int4,
+        mod_user_id -> Int4,
+        other_user_id -> Int4,
+        reason -> Nullable<Text>,
+        banned -> Nullable<Bool>,
+        expires -> Nullable<Timestamp>,
+        when_ -> Timestamp,
+    }
+}
+
+table! {
+    mod_ban_from_community (id) {
+        id -> Int4,
+        mod_user_id -> Int4,
+        other_user_id -> Int4,
+        community_id -> Int4,
+        reason -> Nullable<Text>,
+        banned -> Nullable<Bool>,
+        expires -> Nullable<Timestamp>,
+        when_ -> Timestamp,
+    }
+}
+
+table! {
+    mod_lock_post (id) {
+        id -> Int4,
+        mod_user_id -> Int4,
+        post_id -> Int4,
+        locked -> Nullable<Bool>,
+        when_ -> Timestamp,
+    }
+}
+
+table! {
+    mod_remove_comment (id) {
+        id -> Int4,
+        mod_user_id -> Int4,
+        comment_id -> Int4,
+        reason -> Nullable<Text>,
+        removed -> Nullable<Bool>,
+        when_ -> Timestamp,
+    }
+}
+
+table! {
+    mod_remove_community (id) {
+        id -> Int4,
+        mod_user_id -> Int4,
+        community_id -> Int4,
+        reason -> Nullable<Text>,
+        removed -> Nullable<Bool>,
+        expires -> Nullable<Timestamp>,
+        when_ -> Timestamp,
+    }
+}
+
+table! {
+    mod_remove_post (id) {
+        id -> Int4,
+        mod_user_id -> Int4,
+        post_id -> Int4,
+        reason -> Nullable<Text>,
+        removed -> Nullable<Bool>,
+        when_ -> Timestamp,
+    }
+}
+
+table! {
+    mod_sticky_post (id) {
+        id -> Int4,
+        mod_user_id -> Int4,
+        post_id -> Int4,
+        stickied -> Nullable<Bool>,
+        when_ -> Timestamp,
+    }
+}
+
+table! {
+    post (id) {
+        id -> Int4,
+        name -> Varchar,
+        url -> Nullable<Text>,
+        body -> Nullable<Text>,
+        creator_id -> Int4,
+        community_id -> Int4,
+        removed -> Bool,
+        locked -> Bool,
+        published -> Timestamp,
+        updated -> Nullable<Timestamp>,
+        deleted -> Bool,
+        nsfw -> Bool,
+        stickied -> Bool,
+    }
+}
+
+table! {
+    post_like (id) {
+        id -> Int4,
+        post_id -> Int4,
+        user_id -> Int4,
+        score -> Int2,
+        published -> Timestamp,
+    }
+}
+
+table! {
+    post_read (id) {
+        id -> Int4,
+        post_id -> Int4,
+        user_id -> Int4,
+        published -> Timestamp,
+    }
+}
+
+table! {
+    post_saved (id) {
+        id -> Int4,
+        post_id -> Int4,
+        user_id -> Int4,
+        published -> Timestamp,
+    }
+}
+
+table! {
+    site (id) {
+        id -> Int4,
+        name -> Varchar,
+        description -> Nullable<Text>,
+        creator_id -> Int4,
+        published -> Timestamp,
+        updated -> Nullable<Timestamp>,
+    }
+}
+
+table! {
+    user_ (id) {
+        id -> Int4,
+        name -> Varchar,
+        fedi_name -> Varchar,
+        preferred_username -> Nullable<Varchar>,
+        password_encrypted -> Text,
+        email -> Nullable<Text>,
+        icon -> Nullable<Bytea>,
+        admin -> Bool,
+        banned -> Bool,
+        published -> Timestamp,
+        updated -> Nullable<Timestamp>,
+        show_nsfw -> Bool,
+        theme -> Varchar,
+    }
+}
+
+table! {
+    user_ban (id) {
+        id -> Int4,
+        user_id -> Int4,
+        published -> Timestamp,
+    }
+}
+
+table! {
+    user_mention (id) {
+        id -> Int4,
+        recipient_id -> Int4,
+        comment_id -> Int4,
+        read -> Bool,
+        published -> Timestamp,
+    }
+}
+
+joinable!(comment -> post (post_id));
+joinable!(comment -> user_ (creator_id));
+joinable!(comment_like -> comment (comment_id));
+joinable!(comment_like -> post (post_id));
+joinable!(comment_like -> user_ (user_id));
+joinable!(comment_saved -> comment (comment_id));
+joinable!(comment_saved -> user_ (user_id));
+joinable!(community -> category (category_id));
+joinable!(community -> user_ (creator_id));
+joinable!(community_follower -> community (community_id));
+joinable!(community_follower -> user_ (user_id));
+joinable!(community_moderator -> community (community_id));
+joinable!(community_moderator -> user_ (user_id));
+joinable!(community_user_ban -> community (community_id));
+joinable!(community_user_ban -> user_ (user_id));
+joinable!(mod_add_community -> community (community_id));
+joinable!(mod_ban_from_community -> community (community_id));
+joinable!(mod_lock_post -> post (post_id));
+joinable!(mod_lock_post -> user_ (mod_user_id));
+joinable!(mod_remove_comment -> comment (comment_id));
+joinable!(mod_remove_comment -> user_ (mod_user_id));
+joinable!(mod_remove_community -> community (community_id));
+joinable!(mod_remove_community -> user_ (mod_user_id));
+joinable!(mod_remove_post -> post (post_id));
+joinable!(mod_remove_post -> user_ (mod_user_id));
+joinable!(mod_sticky_post -> post (post_id));
+joinable!(mod_sticky_post -> user_ (mod_user_id));
+joinable!(post -> community (community_id));
+joinable!(post -> user_ (creator_id));
+joinable!(post_like -> post (post_id));
+joinable!(post_like -> user_ (user_id));
+joinable!(post_read -> post (post_id));
+joinable!(post_read -> user_ (user_id));
+joinable!(post_saved -> post (post_id));
+joinable!(post_saved -> user_ (user_id));
+joinable!(site -> user_ (creator_id));
+joinable!(user_ban -> user_ (user_id));
+joinable!(user_mention -> comment (comment_id));
+joinable!(user_mention -> user_ (recipient_id));
+
+allow_tables_to_appear_in_same_query!(
+    category,
+    comment,
+    comment_like,
+    comment_saved,
+    community,
+    community_follower,
+    community_moderator,
+    community_user_ban,
+    mod_add,
+    mod_add_community,
+    mod_ban,
+    mod_ban_from_community,
+    mod_lock_post,
+    mod_remove_comment,
+    mod_remove_community,
+    mod_remove_post,
+    mod_sticky_post,
+    post,
+    post_like,
+    post_read,
+    post_saved,
+    site,
+    user_,
+    user_ban,
+    user_mention,
+);
diff --git a/server/src/db/user_mention.rs b/server/src/db/user_mention.rs
new file mode 100644
index 00000000..d4dc0a51
--- /dev/null
+++ b/server/src/db/user_mention.rs
@@ -0,0 +1,169 @@
+use super::comment::Comment;
+use super::*;
+use crate::schema::user_mention;
+
+#[derive(Queryable, Associations, Identifiable, PartialEq, Debug, Serialize, Deserialize)]
+#[belongs_to(Comment)]
+#[table_name = "user_mention"]
+pub struct UserMention {
+  pub id: i32,
+  pub recipient_id: i32,
+  pub comment_id: i32,
+  pub read: bool,
+  pub published: chrono::NaiveDateTime,
+}
+
+#[derive(Insertable, AsChangeset, Clone)]
+#[table_name = "user_mention"]
+pub struct UserMentionForm {
+  pub recipient_id: i32,
+  pub comment_id: i32,
+  pub read: Option<bool>,
+}
+
+impl Crud<UserMentionForm> for UserMention {
+  fn read(conn: &PgConnection, user_mention_id: i32) -> Result<Self, Error> {
+    use crate::schema::user_mention::dsl::*;
+    user_mention.find(user_mention_id).first::<Self>(conn)
+  }
+
+  fn delete(conn: &PgConnection, user_mention_id: i32) -> Result<usize, Error> {
+    use crate::schema::user_mention::dsl::*;
+    diesel::delete(user_mention.find(user_mention_id)).execute(conn)
+  }
+
+  fn create(conn: &PgConnection, user_mention_form: &UserMentionForm) -> Result<Self, Error> {
+    use crate::schema::user_mention::dsl::*;
+    insert_into(user_mention)
+      .values(user_mention_form)
+      .get_result::<Self>(conn)
+  }
+
+  fn update(
+    conn: &PgConnection,
+    user_mention_id: i32,
+    user_mention_form: &UserMentionForm,
+  ) -> Result<Self, Error> {
+    use crate::schema::user_mention::dsl::*;
+    diesel::update(user_mention.find(user_mention_id))
+      .set(user_mention_form)
+      .get_result::<Self>(conn)
+  }
+}
+
+#[cfg(test)]
+mod tests {
+  use super::super::comment::*;
+  use super::super::community::*;
+  use super::super::post::*;
+  use super::super::user::*;
+  use super::*;
+  #[test]
+  fn test_crud() {
+    let conn = establish_connection();
+
+    let new_user = UserForm {
+      name: "terrylake".into(),
+      fedi_name: "rrf".into(),
+      preferred_username: None,
+      password_encrypted: "nope".into(),
+      email: None,
+      admin: false,
+      banned: false,
+      updated: None,
+      show_nsfw: false,
+      theme: "darkly".into(),
+    };
+
+    let inserted_user = User_::create(&conn, &new_user).unwrap();
+
+    let recipient_form = UserForm {
+      name: "terrylakes recipient".into(),
+      fedi_name: "rrf".into(),
+      preferred_username: None,
+      password_encrypted: "nope".into(),
+      email: None,
+      admin: false,
+      banned: false,
+      updated: None,
+      show_nsfw: false,
+      theme: "darkly".into(),
+    };
+
+    let inserted_recipient = User_::create(&conn, &recipient_form).unwrap();
+
+    let new_community = CommunityForm {
+      name: "test community lake".to_string(),
+      title: "nada".to_owned(),
+      description: None,
+      category_id: 1,
+      creator_id: inserted_user.id,
+      removed: None,
+      deleted: None,
+      updated: None,
+      nsfw: false,
+    };
+
+    let inserted_community = Community::create(&conn, &new_community).unwrap();
+
+    let new_post = PostForm {
+      name: "A test post".into(),
+      creator_id: inserted_user.id,
+      url: None,
+      body: None,
+      community_id: inserted_community.id,
+      removed: None,
+      deleted: None,
+      locked: None,
+      stickied: None,
+      updated: None,
+      nsfw: false,
+    };
+
+    let inserted_post = Post::create(&conn, &new_post).unwrap();
+
+    let comment_form = CommentForm {
+      content: "A test comment".into(),
+      creator_id: inserted_user.id,
+      post_id: inserted_post.id,
+      removed: None,
+      deleted: None,
+      read: None,
+      parent_id: None,
+      updated: None,
+    };
+
+    let inserted_comment = Comment::create(&conn, &comment_form).unwrap();
+
+    let user_mention_form = UserMentionForm {
+      recipient_id: inserted_recipient.id,
+      comment_id: inserted_comment.id,
+      read: None,
+    };
+
+    let inserted_mention = UserMention::create(&conn, &user_mention_form).unwrap();
+
+    let expected_mention = UserMention {
+      id: inserted_mention.id,
+      recipient_id: inserted_mention.recipient_id,
+      comment_id: inserted_mention.comment_id,
+      read: false,
+      published: inserted_mention.published,
+    };
+
+    let read_mention = UserMention::read(&conn, inserted_mention.id).unwrap();
+    let updated_mention =
+      UserMention::update(&conn, inserted_mention.id, &user_mention_form).unwrap();
+    let num_deleted = UserMention::delete(&conn, inserted_mention.id).unwrap();
+    Comment::delete(&conn, inserted_comment.id).unwrap();
+    Post::delete(&conn, inserted_post.id).unwrap();
+    Community::delete(&conn, inserted_community.id).unwrap();
+    User_::delete(&conn, inserted_user.id).unwrap();
+    User_::delete(&conn, inserted_recipient.id).unwrap();
+
+    assert_eq!(expected_mention, read_mention);
+    assert_eq!(expected_mention, inserted_mention);
+    assert_eq!(expected_mention, updated_mention);
+    assert_eq!(1, num_deleted);
+  }
+}
diff --git a/server/src/db/user_mention_view.rs b/server/src/db/user_mention_view.rs
new file mode 100644
index 00000000..6676ab9a
--- /dev/null
+++ b/server/src/db/user_mention_view.rs
@@ -0,0 +1,117 @@
+use super::*;
+
+// The faked schema since diesel doesn't do views
+table! {
+  user_mention_view (id) {
+    id -> Int4,
+    user_mention_id -> Int4,
+    creator_id -> Int4,
+    post_id -> Int4,
+    parent_id -> Nullable<Int4>,
+    content -> Text,
+    removed -> Bool,
+    read -> Bool,
+    published -> Timestamp,
+    updated -> Nullable<Timestamp>,
+    deleted -> Bool,
+    community_id -> Int4,
+    banned -> Bool,
+    banned_from_community -> Bool,
+    creator_name -> Varchar,
+    score -> BigInt,
+    upvotes -> BigInt,
+    downvotes -> BigInt,
+    user_id -> Nullable<Int4>,
+    my_vote -> Nullable<Int4>,
+    saved -> Nullable<Bool>,
+    recipient_id -> Int4,
+  }
+}
+
+#[derive(
+  Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
+)]
+#[table_name = "user_mention_view"]
+pub struct UserMentionView {
+  pub id: i32,
+  pub user_mention_id: i32,
+  pub creator_id: i32,
+  pub post_id: i32,
+  pub parent_id: Option<i32>,
+  pub content: String,
+  pub removed: bool,
+  pub read: bool,
+  pub published: chrono::NaiveDateTime,
+  pub updated: Option<chrono::NaiveDateTime>,
+  pub deleted: bool,
+  pub community_id: i32,
+  pub banned: bool,
+  pub banned_from_community: bool,
+  pub creator_name: String,
+  pub score: i64,
+  pub upvotes: i64,
+  pub downvotes: i64,
+  pub user_id: Option<i32>,
+  pub my_vote: Option<i32>,
+  pub saved: Option<bool>,
+  pub recipient_id: i32,
+}
+
+impl UserMentionView {
+  pub fn get_mentions(
+    conn: &PgConnection,
+    for_user_id: i32,
+    sort: &SortType,
+    unread_only: bool,
+    page: Option<i64>,
+    limit: Option<i64>,
+  ) -> Result<Vec<Self>, Error> {
+    use super::user_mention_view::user_mention_view::dsl::*;
+
+    let (limit, offset) = limit_and_offset(page, limit);
+
+    let mut query = user_mention_view.into_boxed();
+
+    query = query
+      .filter(user_id.eq(for_user_id))
+      .filter(recipient_id.eq(for_user_id));
+
+    if unread_only {
+      query = query.filter(read.eq(false));
+    }
+
+    query = match sort {
+      // SortType::Hot => query.order_by(hot_rank.desc()),
+      SortType::New => query.order_by(published.desc()),
+      SortType::TopAll => query.order_by(score.desc()),
+      SortType::TopYear => query
+        .filter(published.gt(now - 1.years()))
+        .order_by(score.desc()),
+      SortType::TopMonth => query
+        .filter(published.gt(now - 1.months()))
+        .order_by(score.desc()),
+      SortType::TopWeek => query
+        .filter(published.gt(now - 1.weeks()))
+        .order_by(score.desc()),
+      SortType::TopDay => query
+        .filter(published.gt(now - 1.days()))
+        .order_by(score.desc()),
+      _ => query.order_by(published.desc()),
+    };
+
+    query.limit(limit).offset(offset).load::<Self>(conn)
+  }
+
+  pub fn read(
+    conn: &PgConnection,
+    from_user_mention_id: i32,
+    from_recipient_id: i32,
+  ) -> Result<Self, Error> {
+    use super::user_mention_view::user_mention_view::dsl::*;
+
+    user_mention_view
+      .filter(user_mention_id.eq(from_user_mention_id))