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