lemmy_db/src/views/comment_view.rs

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