server/src/db/comment_view.rs

   1 use super::*;
   2 use diesel::pg::Pg;
   3
   4 // The faked schema since diesel doesn't do views
   5 table! {
   6   comment_view (id) {
   7     id -> Int4,
   8     creator_id -> Int4,
   9     post_id -> Int4,
  10     parent_id -> Nullable<Int4>,
  11     content -> Text,
  12     removed -> Bool,
  13     read -> Bool,
  14     published -> Timestamp,
  15     updated -> Nullable<Timestamp>,
  16     deleted -> Bool,
  17     community_id -> Int4,
  18     community_name -> Varchar,
  19     banned -> Bool,
  20     banned_from_community -> Bool,
  21     creator_name -> Varchar,
  22     creator_avatar -> Nullable<Text>,
  23     score -> BigInt,
  24     upvotes -> BigInt,
  25     downvotes -> BigInt,
  26     hot_rank -> Int4,
  27     user_id -> Nullable<Int4>,
  28     my_vote -> Nullable<Int4>,
  29     subscribed -> Nullable<Bool>,
  30     saved -> Nullable<Bool>,
  31   }
  32 }
  33
  34 table! {
  35   comment_mview (id) {
  36     id -> Int4,
  37     creator_id -> Int4,
  38     post_id -> Int4,
  39     parent_id -> Nullable<Int4>,
  40     content -> Text,
  41     removed -> Bool,
  42     read -> Bool,
  43     published -> Timestamp,
  44     updated -> Nullable<Timestamp>,
  45     deleted -> Bool,
  46     community_id -> Int4,
  47     community_name -> Varchar,
  48     banned -> Bool,
  49     banned_from_community -> Bool,
  50     creator_name -> Varchar,
  51     creator_avatar -> Nullable<Text>,
  52     score -> BigInt,
  53     upvotes -> BigInt,
  54     downvotes -> BigInt,
  55     hot_rank -> Int4,
  56     user_id -> Nullable<Int4>,
  57     my_vote -> Nullable<Int4>,
  58     subscribed -> Nullable<Bool>,
  59     saved -> Nullable<Bool>,
  60   }
  61 }
  62
  63 #[derive(
  64   Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
  65 )]
  66 #[table_name = "comment_view"]
  67 pub struct CommentView {
  68   pub id: i32,
  69   pub creator_id: i32,
  70   pub post_id: i32,
  71   pub parent_id: Option<i32>,
  72   pub content: String,
  73   pub removed: bool,
  74   pub read: bool,
  75   pub published: chrono::NaiveDateTime,
  76   pub updated: Option<chrono::NaiveDateTime>,
  77   pub deleted: bool,
  78   pub community_id: i32,
  79   pub community_name: String,
  80   pub banned: bool,
  81   pub banned_from_community: bool,
  82   pub creator_name: String,
  83   pub creator_avatar: Option<String>,
  84   pub score: i64,
  85   pub upvotes: i64,
  86   pub downvotes: i64,
  87   pub hot_rank: i32,
  88   pub user_id: Option<i32>,
  89   pub my_vote: Option<i32>,
  90   pub subscribed: Option<bool>,
  91   pub saved: Option<bool>,
  92 }
  93
  94 pub struct CommentQueryBuilder<'a> {
  95   conn: &'a PgConnection,
  96   query: super::comment_view::comment_mview::BoxedQuery<'a, Pg>,
  97   listing_type: ListingType,
  98   sort: &'a SortType,
  99   for_community_id: Option<i32>,
 100   for_post_id: Option<i32>,
 101   for_creator_id: Option<i32>,
 102   search_term: Option<String>,
 103   my_user_id: Option<i32>,
 104   saved_only: bool,
 105   page: Option<i64>,
 106   limit: Option<i64>,
 107 }
 108
 109 impl<'a> CommentQueryBuilder<'a> {
 110   pub fn create(conn: &'a PgConnection) -> Self {
 111     use super::comment_view::comment_mview::dsl::*;
 112
 113     let query = comment_mview.into_boxed();
 114
 115     CommentQueryBuilder {
 116       conn,
 117       query,
 118       listing_type: ListingType::All,
 119       sort: &SortType::New,
 120       for_community_id: None,
 121       for_post_id: None,
 122       for_creator_id: None,
 123       search_term: None,
 124       my_user_id: None,
 125       saved_only: false,
 126       page: None,
 127       limit: None,
 128     }
 129   }
 130
 131   pub fn listing_type(mut self, listing_type: ListingType) -> Self {
 132     self.listing_type = listing_type;
 133     self
 134   }
 135
 136   pub fn sort(mut self, sort: &'a SortType) -> Self {
 137     self.sort = sort;
 138     self
 139   }
 140
 141   pub fn for_post_id<T: MaybeOptional<i32>>(mut self, for_post_id: T) -> Self {
 142     self.for_post_id = for_post_id.get_optional();
 143     self
 144   }
 145
 146   pub fn for_creator_id<T: MaybeOptional<i32>>(mut self, for_creator_id: T) -> Self {
 147     self.for_creator_id = for_creator_id.get_optional();
 148     self
 149   }
 150
 151   pub fn for_community_id<T: MaybeOptional<i32>>(mut self, for_community_id: T) -> Self {
 152     self.for_community_id = for_community_id.get_optional();
 153     self
 154   }
 155
 156   pub fn search_term<T: MaybeOptional<String>>(mut self, search_term: T) -> Self {
 157     self.search_term = search_term.get_optional();
 158     self
 159   }
 160
 161   pub fn my_user_id<T: MaybeOptional<i32>>(mut self, my_user_id: T) -> Self {
 162     self.my_user_id = my_user_id.get_optional();
 163     self
 164   }
 165
 166   pub fn saved_only(mut self, saved_only: bool) -> Self {
 167     self.saved_only = saved_only;
 168     self
 169   }
 170
 171   pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
 172     self.page = page.get_optional();
 173     self
 174   }
 175
 176   pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
 177     self.limit = limit.get_optional();
 178     self
 179   }
 180
 181   pub fn list(self) -> Result<Vec<CommentView>, Error> {
 182     use super::comment_view::comment_mview::dsl::*;
 183
 184     let mut query = self.query;
 185
 186     // The view lets you pass a null user_id, if you're not logged in
 187     if let Some(my_user_id) = self.my_user_id {
 188       query = query.filter(user_id.eq(my_user_id));
 189     } else {
 190       query = query.filter(user_id.is_null());
 191     }
 192
 193     if let Some(for_creator_id) = self.for_creator_id {
 194       query = query.filter(creator_id.eq(for_creator_id));
 195     };
 196
 197     if let Some(for_community_id) = self.for_community_id {
 198       query = query.filter(community_id.eq(for_community_id));
 199     }
 200
 201     if let Some(for_post_id) = self.for_post_id {
 202       query = query.filter(post_id.eq(for_post_id));
 203     };
 204
 205     if let Some(search_term) = self.search_term {
 206       query = query.filter(content.ilike(fuzzy_search(&search_term)));
 207     };
 208
 209     if let ListingType::Subscribed = self.listing_type {
 210       query = query.filter(subscribed.eq(true));
 211     }
 212
 213     if self.saved_only {
 214       query = query.filter(saved.eq(true));
 215     }
 216
 217     query = match self.sort {
 218       SortType::Hot => query
 219         .order_by(hot_rank.desc())
 220         .then_order_by(published.desc()),
 221       SortType::New => query.order_by(published.desc()),
 222       SortType::TopAll => query.order_by(score.desc()),
 223       SortType::TopYear => query
 224         .filter(published.gt(now - 1.years()))
 225         .order_by(score.desc()),
 226       SortType::TopMonth => query
 227         .filter(published.gt(now - 1.months()))
 228         .order_by(score.desc()),
 229       SortType::TopWeek => query
 230         .filter(published.gt(now - 1.weeks()))
 231         .order_by(score.desc()),
 232       SortType::TopDay => query
 233         .filter(published.gt(now - 1.days()))
 234         .order_by(score.desc()),
 235       // _ => query.order_by(published.desc()),
 236     };
 237
 238     let (limit, offset) = limit_and_offset(self.page, self.limit);
 239
 240     // Note: deleted and removed comments are done on the front side
 241     query
 242       .limit(limit)
 243       .offset(offset)
 244       .load::<CommentView>(self.conn)
 245   }
 246 }
 247
 248 impl CommentView {
 249   pub fn read(
 250     conn: &PgConnection,
 251     from_comment_id: i32,
 252     my_user_id: Option<i32>,
 253   ) -> Result<Self, Error> {
 254     use super::comment_view::comment_mview::dsl::*;
 255     let mut query = comment_mview.into_boxed();
 256
 257     // The view lets you pass a null user_id, if you're not logged in
 258     if let Some(my_user_id) = my_user_id {
 259       query = query.filter(user_id.eq(my_user_id));
 260     } else {
 261       query = query.filter(user_id.is_null());
 262     }
 263
 264     query = query
 265       .filter(id.eq(from_comment_id))
 266       .order_by(published.desc());
 267
 268     query.first::<Self>(conn)
 269   }
 270 }
 271
 272 // The faked schema since diesel doesn't do views
 273 table! {
 274   reply_view (id) {
 275     id -> Int4,
 276     creator_id -> Int4,
 277     post_id -> Int4,
 278     parent_id -> Nullable<Int4>,
 279     content -> Text,
 280     removed -> Bool,
 281     read -> Bool,
 282     published -> Timestamp,
 283     updated -> Nullable<Timestamp>,
 284     deleted -> Bool,
 285     community_id -> Int4,
 286     community_name -> Varchar,
 287     banned -> Bool,
 288     banned_from_community -> Bool,
 289     creator_name -> Varchar,
 290     creator_avatar -> Nullable<Text>,
 291     score -> BigInt,
 292     upvotes -> BigInt,
 293     downvotes -> BigInt,
 294     hot_rank -> Int4,
 295     user_id -> Nullable<Int4>,
 296     my_vote -> Nullable<Int4>,
 297     subscribed -> Nullable<Bool>,
 298     saved -> Nullable<Bool>,
 299     recipient_id -> Int4,
 300   }
 301 }
 302
 303 #[derive(
 304   Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
 305 )]
 306 #[table_name = "reply_view"]
 307 pub struct ReplyView {
 308   pub id: i32,
 309   pub creator_id: i32,
 310   pub post_id: i32,
 311   pub parent_id: Option<i32>,
 312   pub content: String,
 313   pub removed: bool,
 314   pub read: bool,
 315   pub published: chrono::NaiveDateTime,
 316   pub updated: Option<chrono::NaiveDateTime>,
 317   pub deleted: bool,
 318   pub community_id: i32,
 319   pub community_name: String,
 320   pub banned: bool,
 321   pub banned_from_community: bool,
 322   pub creator_name: String,
 323   pub creator_avatar: Option<String>,
 324   pub score: i64,
 325   pub upvotes: i64,
 326   pub downvotes: i64,
 327   pub hot_rank: i32,
 328   pub user_id: Option<i32>,
 329   pub my_vote: Option<i32>,
 330   pub subscribed: Option<bool>,
 331   pub saved: Option<bool>,
 332   pub recipient_id: i32,
 333 }
 334
 335 pub struct ReplyQueryBuilder<'a> {
 336   conn: &'a PgConnection,
 337   query: super::comment_view::reply_view::BoxedQuery<'a, Pg>,
 338   for_user_id: i32,
 339   sort: &'a SortType,
 340   unread_only: bool,
 341   page: Option<i64>,
 342   limit: Option<i64>,
 343 }
 344
 345 impl<'a> ReplyQueryBuilder<'a> {
 346   pub fn create(conn: &'a PgConnection, for_user_id: i32) -> Self {
 347     use super::comment_view::reply_view::dsl::*;
 348
 349     let query = reply_view.into_boxed();
 350
 351     ReplyQueryBuilder {
 352       conn,
 353       query,
 354       for_user_id,
 355       sort: &SortType::New,
 356       unread_only: false,
 357       page: None,
 358       limit: None,
 359     }
 360   }
 361
 362   pub fn sort(mut self, sort: &'a SortType) -> Self {
 363     self.sort = sort;
 364     self
 365   }
 366
 367   pub fn unread_only(mut self, unread_only: bool) -> Self {
 368     self.unread_only = unread_only;
 369     self
 370   }
 371
 372   pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
 373     self.page = page.get_optional();
 374     self
 375   }
 376
 377   pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
 378     self.limit = limit.get_optional();
 379     self
 380   }
 381
 382   pub fn list(self) -> Result<Vec<ReplyView>, Error> {
 383     use super::comment_view::reply_view::dsl::*;
 384
 385     let mut query = self.query;
 386
 387     query = query
 388       .filter(user_id.eq(self.for_user_id))
 389       .filter(recipient_id.eq(self.for_user_id))
 390       .filter(deleted.eq(false))
 391       .filter(removed.eq(false));
 392
 393     if self.unread_only {
 394       query = query.filter(read.eq(false));
 395     }
 396
 397     query = match self.sort {
 398       // SortType::Hot => query.order_by(hot_rank.desc()),
 399       SortType::New => query.order_by(published.desc()),
 400       SortType::TopAll => query.order_by(score.desc()),
 401       SortType::TopYear => query
 402         .filter(published.gt(now - 1.years()))
 403         .order_by(score.desc()),
 404       SortType::TopMonth => query
 405         .filter(published.gt(now - 1.months()))
 406         .order_by(score.desc()),
 407       SortType::TopWeek => query
 408         .filter(published.gt(now - 1.weeks()))
 409         .order_by(score.desc()),
 410       SortType::TopDay => query
 411         .filter(published.gt(now - 1.days()))
 412         .order_by(score.desc()),
 413       _ => query.order_by(published.desc()),
 414     };
 415
 416     let (limit, offset) = limit_and_offset(self.page, self.limit);
 417     query
 418       .limit(limit)
 419       .offset(offset)
 420       .load::<ReplyView>(self.conn)
 421   }
 422 }
 423
 424 #[cfg(test)]
 425 mod tests {
 426   use super::super::comment::*;
 427   use super::super::community::*;
 428   use super::super::post::*;
 429   use super::super::user::*;
 430   use super::*;
 431   #[test]
 432   fn test_crud() {
 433     let conn = establish_unpooled_connection();
 434
 435     let new_user = UserForm {
 436       name: "timmy".into(),
 437       fedi_name: "rrf".into(),
 438       preferred_username: None,
 439       password_encrypted: "nope".into(),
 440       email: None,
 441       matrix_user_id: None,
 442       avatar: None,
 443       admin: false,
 444       banned: false,
 445       updated: None,
 446       show_nsfw: false,
 447       theme: "darkly".into(),
 448       default_sort_type: SortType::Hot as i16,
 449       default_listing_type: ListingType::Subscribed as i16,
 450       lang: "browser".into(),
 451       show_avatars: true,
 452       send_notifications_to_email: false,
 453     };
 454
 455     let inserted_user = User_::create(&conn, &new_user).unwrap();
 456
 457     let new_community = CommunityForm {
 458       name: "test community 5".to_string(),
 459       title: "nada".to_owned(),
 460       description: None,
 461       category_id: 1,
 462       creator_id: inserted_user.id,
 463       removed: None,
 464       deleted: None,
 465       updated: None,
 466       nsfw: false,
 467     };
 468
 469     let inserted_community = Community::create(&conn, &new_community).unwrap();
 470
 471     let new_post = PostForm {
 472       name: "A test post 2".into(),
 473       creator_id: inserted_user.id,
 474       url: None,
 475       body: None,
 476       community_id: inserted_community.id,
 477       removed: None,
 478       deleted: None,
 479       locked: None,
 480       stickied: None,
 481       updated: None,
 482       nsfw: false,
 483       embed_title: None,
 484       embed_description: None,
 485       embed_html: None,
 486       thumbnail_url: None,
 487     };
 488
 489     let inserted_post = Post::create(&conn, &new_post).unwrap();
 490
 491     let comment_form = CommentForm {
 492       content: "A test comment 32".into(),
 493       creator_id: inserted_user.id,
 494       post_id: inserted_post.id,
 495       parent_id: None,
 496       removed: None,
 497       deleted: None,
 498       read: None,
 499       updated: None,
 500     };
 501
 502     let inserted_comment = Comment::create(&conn, &comment_form).unwrap();
 503
 504     let comment_like_form = CommentLikeForm {
 505       comment_id: inserted_comment.id,
 506       post_id: inserted_post.id,
 507       user_id: inserted_user.id,
 508       score: 1,
 509     };
 510
 511     let _inserted_comment_like = CommentLike::like(&conn, &comment_like_form).unwrap();
 512
 513     let expected_comment_view_no_user = CommentView {
 514       id: inserted_comment.id,
 515       content: "A test comment 32".into(),
 516       creator_id: inserted_user.id,
 517       post_id: inserted_post.id,
 518       community_id: inserted_community.id,
 519       community_name: inserted_community.name.to_owned(),
 520       parent_id: None,
 521       removed: false,
 522       deleted: false,
 523       read: false,
 524       banned: false,
 525       banned_from_community: false,
 526       published: inserted_comment.published,
 527       updated: None,
 528       creator_name: inserted_user.name.to_owned(),
 529       creator_avatar: None,
 530       score: 1,
 531       downvotes: 0,
 532       hot_rank: 0,
 533       upvotes: 1,
 534       user_id: None,
 535       my_vote: None,
 536       subscribed: None,
 537       saved: None,
 538     };
 539
 540     let expected_comment_view_with_user = CommentView {
 541       id: inserted_comment.id,
 542       content: "A test comment 32".into(),
 543       creator_id: inserted_user.id,
 544       post_id: inserted_post.id,
 545       community_id: inserted_community.id,
 546       community_name: inserted_community.name.to_owned(),
 547       parent_id: None,
 548       removed: false,
 549       deleted: false,
 550       read: false,
 551       banned: false,
 552       banned_from_community: false,
 553       published: inserted_comment.published,
 554       updated: None,
 555       creator_name: inserted_user.name.to_owned(),
 556       creator_avatar: None,
 557       score: 1,
 558       downvotes: 0,
 559       hot_rank: 0,
 560       upvotes: 1,
 561       user_id: Some(inserted_user.id),
 562       my_vote: Some(1),
 563       subscribed: None,
 564       saved: None,
 565     };
 566
 567     let mut read_comment_views_no_user = CommentQueryBuilder::create(&conn)
 568       .for_post_id(inserted_post.id)
 569       .list()
 570       .unwrap();
 571     read_comment_views_no_user[0].hot_rank = 0;
 572
 573     let mut read_comment_views_with_user = CommentQueryBuilder::create(&conn)
 574       .for_post_id(inserted_post.id)
 575       .my_user_id(inserted_user.id)
 576       .list()
 577       .unwrap();
 578     read_comment_views_with_user[0].hot_rank = 0;
 579
 580     let like_removed = CommentLike::remove(&conn, &comment_like_form).unwrap();
 581     let num_deleted = Comment::delete(&conn, inserted_comment.id).unwrap();
 582     Post::delete(&conn, inserted_post.id).unwrap();
 583     Community::delete(&conn, inserted_community.id).unwrap();
 584     User_::delete(&conn, inserted_user.id).unwrap();
 585
 586     assert_eq!(expected_comment_view_no_user, read_comment_views_no_user[0]);
 587     assert_eq!(
 588       expected_comment_view_with_user,
 589       read_comment_views_with_user[0]
 590     );
 591     assert_eq!(1, num_deleted);
 592     assert_eq!(1, like_removed);
 593   }
 594 }