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