server/lemmy_db/src/comment_view.rs

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