crates/db_views/src/post_view.rs

   1 use crate::structs::PostView;
   2 use diesel::{dsl::*, pg::Pg, result::Error, *};
   3 use lemmy_db_schema::{
   4   aggregates::structs::PostAggregates,
   5   newtypes::{CommunityId, DbUrl, PersonId, PostId},
   6   schema::{
   7     community,
   8     community_block,
   9     community_follower,
  10     community_person_ban,
  11     person,
  12     person_block,
  13     post,
  14     post_aggregates,
  15     post_like,
  16     post_read,
  17     post_saved,
  18   },
  19   source::{
  20     community::{Community, CommunityFollower, CommunityPersonBan, CommunitySafe},
  21     person::{Person, PersonSafe},
  22     person_block::PersonBlock,
  23     post::{Post, PostRead, PostSaved},
  24   },
  25   traits::{ToSafe, ViewToVec},
  26   utils::{functions::hot_rank, fuzzy_search, limit_and_offset},
  27   ListingType,
  28   SortType,
  29 };
  30 use tracing::debug;
  31 use typed_builder::TypedBuilder;
  32
  33 type PostViewTuple = (
  34   Post,
  35   PersonSafe,
  36   CommunitySafe,
  37   Option<CommunityPersonBan>,
  38   PostAggregates,
  39   Option<CommunityFollower>,
  40   Option<PostSaved>,
  41   Option<PostRead>,
  42   Option<PersonBlock>,
  43   Option<i16>,
  44 );
  45
  46 impl PostView {
  47   pub fn read(
  48     conn: &PgConnection,
  49     post_id: PostId,
  50     my_person_id: Option<PersonId>,
  51   ) -> Result<Self, Error> {
  52     // The left join below will return None in this case
  53     let person_id_join = my_person_id.unwrap_or(PersonId(-1));
  54
  55     let (
  56       post,
  57       creator,
  58       community,
  59       creator_banned_from_community,
  60       counts,
  61       follower,
  62       saved,
  63       read,
  64       creator_blocked,
  65       post_like,
  66     ) = post::table
  67       .find(post_id)
  68       .inner_join(person::table)
  69       .inner_join(community::table)
  70       .left_join(
  71         community_person_ban::table.on(
  72           post::community_id
  73             .eq(community_person_ban::community_id)
  74             .and(community_person_ban::person_id.eq(post::creator_id))
  75             .and(
  76               community_person_ban::expires
  77                 .is_null()
  78                 .or(community_person_ban::expires.gt(now)),
  79             ),
  80         ),
  81       )
  82       .inner_join(post_aggregates::table)
  83       .left_join(
  84         community_follower::table.on(
  85           post::community_id
  86             .eq(community_follower::community_id)
  87             .and(community_follower::person_id.eq(person_id_join)),
  88         ),
  89       )
  90       .left_join(
  91         post_saved::table.on(
  92           post::id
  93             .eq(post_saved::post_id)
  94             .and(post_saved::person_id.eq(person_id_join)),
  95         ),
  96       )
  97       .left_join(
  98         post_read::table.on(
  99           post::id
 100             .eq(post_read::post_id)
 101             .and(post_read::person_id.eq(person_id_join)),
 102         ),
 103       )
 104       .left_join(
 105         person_block::table.on(
 106           post::creator_id
 107             .eq(person_block::target_id)
 108             .and(person_block::person_id.eq(person_id_join)),
 109         ),
 110       )
 111       .left_join(
 112         post_like::table.on(
 113           post::id
 114             .eq(post_like::post_id)
 115             .and(post_like::person_id.eq(person_id_join)),
 116         ),
 117       )
 118       .select((
 119         post::all_columns,
 120         Person::safe_columns_tuple(),
 121         Community::safe_columns_tuple(),
 122         community_person_ban::all_columns.nullable(),
 123         post_aggregates::all_columns,
 124         community_follower::all_columns.nullable(),
 125         post_saved::all_columns.nullable(),
 126         post_read::all_columns.nullable(),
 127         person_block::all_columns.nullable(),
 128         post_like::score.nullable(),
 129       ))
 130       .first::<PostViewTuple>(conn)?;
 131
 132     // If a person is given, then my_vote, if None, should be 0, not null
 133     // Necessary to differentiate between other person's votes
 134     let my_vote = if my_person_id.is_some() && post_like.is_none() {
 135       Some(0)
 136     } else {
 137       post_like
 138     };
 139
 140     Ok(PostView {
 141       post,
 142       creator,
 143       community,
 144       creator_banned_from_community: creator_banned_from_community.is_some(),
 145       counts,
 146       subscribed: CommunityFollower::to_subscribed_type(&follower),
 147       saved: saved.is_some(),
 148       read: read.is_some(),
 149       creator_blocked: creator_blocked.is_some(),
 150       my_vote,
 151     })
 152   }
 153 }
 154
 155 #[derive(TypedBuilder)]
 156 #[builder(field_defaults(default))]
 157 pub struct PostQuery<'a> {
 158   #[builder(!default)]
 159   conn: &'a PgConnection,
 160   listing_type: Option<ListingType>,
 161   sort: Option<SortType>,
 162   creator_id: Option<PersonId>,
 163   community_id: Option<CommunityId>,
 164   community_actor_id: Option<DbUrl>,
 165   my_person_id: Option<PersonId>,
 166   search_term: Option<String>,
 167   url_search: Option<String>,
 168   show_nsfw: Option<bool>,
 169   show_bot_accounts: Option<bool>,
 170   show_read_posts: Option<bool>,
 171   saved_only: Option<bool>,
 172   page: Option<i64>,
 173   limit: Option<i64>,
 174 }
 175
 176 impl<'a> PostQuery<'a> {
 177   pub fn list(self) -> Result<Vec<PostView>, Error> {
 178     use diesel::dsl::*;
 179
 180     // The left join below will return None in this case
 181     let person_id_join = self.my_person_id.unwrap_or(PersonId(-1));
 182
 183     let mut query = post::table
 184       .inner_join(person::table)
 185       .inner_join(community::table)
 186       .left_join(
 187         community_person_ban::table.on(
 188           post::community_id
 189             .eq(community_person_ban::community_id)
 190             .and(community_person_ban::person_id.eq(post::creator_id))
 191             .and(
 192               community_person_ban::expires
 193                 .is_null()
 194                 .or(community_person_ban::expires.gt(now)),
 195             ),
 196         ),
 197       )
 198       .inner_join(post_aggregates::table)
 199       .left_join(
 200         community_follower::table.on(
 201           post::community_id
 202             .eq(community_follower::community_id)
 203             .and(community_follower::person_id.eq(person_id_join)),
 204         ),
 205       )
 206       .left_join(
 207         post_saved::table.on(
 208           post::id
 209             .eq(post_saved::post_id)
 210             .and(post_saved::person_id.eq(person_id_join)),
 211         ),
 212       )
 213       .left_join(
 214         post_read::table.on(
 215           post::id
 216             .eq(post_read::post_id)
 217             .and(post_read::person_id.eq(person_id_join)),
 218         ),
 219       )
 220       .left_join(
 221         person_block::table.on(
 222           post::creator_id
 223             .eq(person_block::target_id)
 224             .and(person_block::person_id.eq(person_id_join)),
 225         ),
 226       )
 227       .left_join(
 228         community_block::table.on(
 229           community::id
 230             .eq(community_block::community_id)
 231             .and(community_block::person_id.eq(person_id_join)),
 232         ),
 233       )
 234       .left_join(
 235         post_like::table.on(
 236           post::id
 237             .eq(post_like::post_id)
 238             .and(post_like::person_id.eq(person_id_join)),
 239         ),
 240       )
 241       .select((
 242         post::all_columns,
 243         Person::safe_columns_tuple(),
 244         Community::safe_columns_tuple(),
 245         community_person_ban::all_columns.nullable(),
 246         post_aggregates::all_columns,
 247         community_follower::all_columns.nullable(),
 248         post_saved::all_columns.nullable(),
 249         post_read::all_columns.nullable(),
 250         person_block::all_columns.nullable(),
 251         post_like::score.nullable(),
 252       ))
 253       .into_boxed();
 254
 255     if let Some(listing_type) = self.listing_type {
 256       match listing_type {
 257         ListingType::Subscribed => {
 258           query = query.filter(community_follower::person_id.is_not_null())
 259         }
 260         ListingType::Local => {
 261           query = query.filter(community::local.eq(true)).filter(
 262             community::hidden
 263               .eq(false)
 264               .or(community_follower::person_id.eq(person_id_join)),
 265           );
 266         }
 267         ListingType::All => {
 268           query = query.filter(
 269             community::hidden
 270               .eq(false)
 271               .or(community_follower::person_id.eq(person_id_join)),
 272           )
 273         }
 274       }
 275     }
 276
 277     if let Some(community_id) = self.community_id {
 278       query = query
 279         .filter(post::community_id.eq(community_id))
 280         .then_order_by(post_aggregates::stickied.desc());
 281     }
 282
 283     if let Some(community_actor_id) = self.community_actor_id {
 284       query = query
 285         .filter(community::actor_id.eq(community_actor_id))
 286         .then_order_by(post_aggregates::stickied.desc());
 287     }
 288
 289     if let Some(url_search) = self.url_search {
 290       query = query.filter(post::url.eq(url_search));
 291     }
 292
 293     if let Some(search_term) = self.search_term {
 294       let searcher = fuzzy_search(&search_term);
 295       query = query.filter(
 296         post::name
 297           .ilike(searcher.to_owned())
 298           .or(post::body.ilike(searcher)),
 299       );
 300     }
 301
 302     // If its for a specific person, show the removed / deleted
 303     if let Some(creator_id) = self.creator_id {
 304       query = query.filter(post::creator_id.eq(creator_id));
 305     }
 306
 307     if !self.show_nsfw.unwrap_or(false) {
 308       query = query
 309         .filter(post::nsfw.eq(false))
 310         .filter(community::nsfw.eq(false));
 311     };
 312
 313     if !self.show_bot_accounts.unwrap_or(true) {
 314       query = query.filter(person::bot_account.eq(false));
 315     };
 316
 317     if self.saved_only.unwrap_or(false) {
 318       query = query.filter(post_saved::id.is_not_null());
 319     }
 320     // Only hide the read posts, if the saved_only is false. Otherwise ppl with the hide_read
 321     // setting wont be able to see saved posts.
 322     else if !self.show_read_posts.unwrap_or(true) {
 323       query = query.filter(post_read::id.is_null());
 324     }
 325
 326     // Don't show blocked communities or persons
 327     if self.my_person_id.is_some() {
 328       query = query.filter(community_block::person_id.is_null());
 329       query = query.filter(person_block::person_id.is_null());
 330     }
 331
 332     query = match self.sort.unwrap_or(SortType::Hot) {
 333       SortType::Active => query
 334         .then_order_by(
 335           hot_rank(
 336             post_aggregates::score,
 337             post_aggregates::newest_comment_time_necro,
 338           )
 339           .desc(),
 340         )
 341         .then_order_by(post_aggregates::newest_comment_time_necro.desc()),
 342       SortType::Hot => query
 343         .then_order_by(hot_rank(post_aggregates::score, post_aggregates::published).desc())
 344         .then_order_by(post_aggregates::published.desc()),
 345       SortType::New => query.then_order_by(post_aggregates::published.desc()),
 346       SortType::Old => query.then_order_by(post_aggregates::published.asc()),
 347       SortType::NewComments => query.then_order_by(post_aggregates::newest_comment_time.desc()),
 348       SortType::MostComments => query
 349         .then_order_by(post_aggregates::comments.desc())
 350         .then_order_by(post_aggregates::published.desc()),
 351       SortType::TopAll => query
 352         .then_order_by(post_aggregates::score.desc())
 353         .then_order_by(post_aggregates::published.desc()),
 354       SortType::TopYear => query
 355         .filter(post_aggregates::published.gt(now - 1.years()))
 356         .then_order_by(post_aggregates::score.desc())
 357         .then_order_by(post_aggregates::published.desc()),
 358       SortType::TopMonth => query
 359         .filter(post_aggregates::published.gt(now - 1.months()))
 360         .then_order_by(post_aggregates::score.desc())
 361         .then_order_by(post_aggregates::published.desc()),
 362       SortType::TopWeek => query
 363         .filter(post_aggregates::published.gt(now - 1.weeks()))
 364         .then_order_by(post_aggregates::score.desc())
 365         .then_order_by(post_aggregates::published.desc()),
 366       SortType::TopDay => query
 367         .filter(post_aggregates::published.gt(now - 1.days()))
 368         .then_order_by(post_aggregates::score.desc())
 369         .then_order_by(post_aggregates::published.desc()),
 370     };
 371
 372     let (limit, offset) = limit_and_offset(self.page, self.limit)?;
 373
 374     query = query
 375       .limit(limit)
 376       .offset(offset)
 377       .filter(post::removed.eq(false))
 378       .filter(post::deleted.eq(false))
 379       .filter(community::removed.eq(false))
 380       .filter(community::deleted.eq(false));
 381
 382     debug!("Post View Query: {:?}", debug_query::<Pg, _>(&query));
 383
 384     let res = query.load::<PostViewTuple>(self.conn)?;
 385
 386     Ok(PostView::from_tuple_to_vec(res))
 387   }
 388 }
 389
 390 impl ViewToVec for PostView {
 391   type DbTuple = PostViewTuple;
 392   fn from_tuple_to_vec(items: Vec<Self::DbTuple>) -> Vec<Self> {
 393     items
 394       .into_iter()
 395       .map(|a| Self {
 396         post: a.0,
 397         creator: a.1,
 398         community: a.2,
 399         creator_banned_from_community: a.3.is_some(),
 400         counts: a.4,
 401         subscribed: CommunityFollower::to_subscribed_type(&a.5),
 402         saved: a.6.is_some(),
 403         read: a.7.is_some(),
 404         creator_blocked: a.8.is_some(),
 405         my_vote: a.9,
 406       })
 407       .collect::<Vec<Self>>()
 408   }
 409 }
 410
 411 #[cfg(test)]
 412 mod tests {
 413   use crate::post_view::{PostQuery, PostView};
 414   use lemmy_db_schema::{
 415     aggregates::structs::PostAggregates,
 416     source::{
 417       community::*,
 418       community_block::{CommunityBlock, CommunityBlockForm},
 419       person::*,
 420       person_block::{PersonBlock, PersonBlockForm},
 421       post::*,
 422     },
 423     traits::{Blockable, Crud, Likeable},
 424     utils::establish_unpooled_connection,
 425     SortType,
 426     SubscribedType,
 427   };
 428   use serial_test::serial;
 429
 430   #[test]
 431   #[serial]
 432   fn test_crud() {
 433     let conn = establish_unpooled_connection();
 434
 435     let person_name = "tegan".to_string();
 436     let community_name = "test_community_3".to_string();
 437     let post_name = "test post 3".to_string();
 438     let bot_post_name = "test bot post".to_string();
 439
 440     let new_person = PersonForm {
 441       name: person_name.to_owned(),
 442       public_key: Some("pubkey".to_string()),
 443       ..PersonForm::default()
 444     };
 445
 446     let inserted_person = Person::create(&conn, &new_person).unwrap();
 447
 448     let new_bot = PersonForm {
 449       name: person_name.to_owned(),
 450       bot_account: Some(true),
 451       public_key: Some("pubkey".to_string()),
 452       ..PersonForm::default()
 453     };
 454
 455     let inserted_bot = Person::create(&conn, &new_bot).unwrap();
 456
 457     let new_community = CommunityForm {
 458       name: community_name.to_owned(),
 459       title: "nada".to_owned(),
 460       public_key: Some("pubkey".to_string()),
 461       ..CommunityForm::default()
 462     };
 463
 464     let inserted_community = Community::create(&conn, &new_community).unwrap();
 465
 466     // Test a person block, make sure the post query doesn't include their post
 467     let blocked_person = PersonForm {
 468       name: person_name.to_owned(),
 469       public_key: Some("pubkey".to_string()),
 470       ..PersonForm::default()
 471     };
 472
 473     let inserted_blocked_person = Person::create(&conn, &blocked_person).unwrap();
 474
 475     let post_from_blocked_person = PostForm {
 476       name: "blocked_person_post".to_string(),
 477       creator_id: inserted_blocked_person.id,
 478       community_id: inserted_community.id,
 479       ..PostForm::default()
 480     };
 481
 482     Post::create(&conn, &post_from_blocked_person).unwrap();
 483
 484     // block that person
 485     let person_block = PersonBlockForm {
 486       person_id: inserted_person.id,
 487       target_id: inserted_blocked_person.id,
 488     };
 489
 490     PersonBlock::block(&conn, &person_block).unwrap();
 491
 492     // A sample post
 493     let new_post = PostForm {
 494       name: post_name.to_owned(),
 495       creator_id: inserted_person.id,
 496       community_id: inserted_community.id,
 497       ..PostForm::default()
 498     };
 499
 500     let inserted_post = Post::create(&conn, &new_post).unwrap();
 501
 502     let new_bot_post = PostForm {
 503       name: bot_post_name,
 504       creator_id: inserted_bot.id,
 505       community_id: inserted_community.id,
 506       ..PostForm::default()
 507     };
 508
 509     let _inserted_bot_post = Post::create(&conn, &new_bot_post).unwrap();
 510
 511     let post_like_form = PostLikeForm {
 512       post_id: inserted_post.id,
 513       person_id: inserted_person.id,
 514       score: 1,
 515     };
 516
 517     let inserted_post_like = PostLike::like(&conn, &post_like_form).unwrap();
 518
 519     let expected_post_like = PostLike {
 520       id: inserted_post_like.id,
 521       post_id: inserted_post.id,
 522       person_id: inserted_person.id,
 523       published: inserted_post_like.published,
 524       score: 1,
 525     };
 526
 527     let read_post_listings_with_person = PostQuery::builder()
 528       .conn(&conn)
 529       .sort(Some(SortType::New))
 530       .show_bot_accounts(Some(false))
 531       .community_id(Some(inserted_community.id))
 532       .my_person_id(Some(inserted_person.id))
 533       .build()
 534       .list()
 535       .unwrap();
 536
 537     let read_post_listings_no_person = PostQuery::builder()
 538       .conn(&conn)
 539       .sort(Some(SortType::New))
 540       .community_id(Some(inserted_community.id))
 541       .build()
 542       .list()
 543       .unwrap();
 544
 545     let read_post_listing_no_person = PostView::read(&conn, inserted_post.id, None).unwrap();
 546     let read_post_listing_with_person =
 547       PostView::read(&conn, inserted_post.id, Some(inserted_person.id)).unwrap();
 548
 549     let agg = PostAggregates::read(&conn, inserted_post.id).unwrap();
 550
 551     // the non person version
 552     let expected_post_listing_no_person = PostView {
 553       post: Post {
 554         id: inserted_post.id,
 555         name: post_name,
 556         creator_id: inserted_person.id,
 557         url: None,
 558         body: None,
 559         published: inserted_post.published,
 560         updated: None,
 561         community_id: inserted_community.id,
 562         removed: false,
 563         deleted: false,
 564         locked: false,
 565         stickied: false,
 566         nsfw: false,
 567         embed_title: None,
 568         embed_description: None,
 569         embed_video_url: None,
 570         thumbnail_url: None,
 571         ap_id: inserted_post.ap_id.to_owned(),
 572         local: true,
 573       },
 574       my_vote: None,
 575       creator: PersonSafe {
 576         id: inserted_person.id,
 577         name: person_name,
 578         display_name: None,
 579         published: inserted_person.published,
 580         avatar: None,
 581         actor_id: inserted_person.actor_id.to_owned(),
 582         local: true,
 583         admin: false,
 584         bot_account: false,
 585         banned: false,
 586         deleted: false,
 587         bio: None,
 588         banner: None,
 589         updated: None,
 590         inbox_url: inserted_person.inbox_url.to_owned(),
 591         shared_inbox_url: None,
 592         matrix_user_id: None,
 593         ban_expires: None,
 594       },
 595       creator_banned_from_community: false,
 596       community: CommunitySafe {
 597         id: inserted_community.id,
 598         name: community_name,
 599         icon: None,
 600         removed: false,
 601         deleted: false,
 602         nsfw: false,
 603         actor_id: inserted_community.actor_id.to_owned(),
 604         local: true,
 605         title: "nada".to_owned(),
 606         description: None,
 607         updated: None,
 608         banner: None,
 609         hidden: false,
 610         posting_restricted_to_mods: false,
 611         published: inserted_community.published,
 612       },
 613       counts: PostAggregates {
 614         id: agg.id,
 615         post_id: inserted_post.id,
 616         comments: 0,
 617         score: 1,
 618         upvotes: 1,
 619         downvotes: 0,
 620         stickied: false,
 621         published: agg.published,
 622         newest_comment_time_necro: inserted_post.published,
 623         newest_comment_time: inserted_post.published,
 624       },
 625       subscribed: SubscribedType::NotSubscribed,
 626       read: false,
 627       saved: false,
 628       creator_blocked: false,
 629     };
 630
 631     // Test a community block
 632     let community_block = CommunityBlockForm {
 633       person_id: inserted_person.id,
 634       community_id: inserted_community.id,
 635     };
 636     CommunityBlock::block(&conn, &community_block).unwrap();
 637
 638     let read_post_listings_with_person_after_block = PostQuery::builder()
 639       .conn(&conn)
 640       .sort(Some(SortType::New))
 641       .show_bot_accounts(Some(false))
 642       .community_id(Some(inserted_community.id))
 643       .my_person_id(Some(inserted_person.id))
 644       .build()
 645       .list()
 646       .unwrap();
 647
 648     // TODO More needs to be added here
 649     let mut expected_post_listing_with_user = expected_post_listing_no_person.to_owned();
 650     expected_post_listing_with_user.my_vote = Some(1);
 651
 652     let like_removed = PostLike::remove(&conn, inserted_person.id, inserted_post.id).unwrap();
 653     let num_deleted = Post::delete(&conn, inserted_post.id).unwrap();
 654     PersonBlock::unblock(&conn, &person_block).unwrap();
 655     CommunityBlock::unblock(&conn, &community_block).unwrap();
 656     Community::delete(&conn, inserted_community.id).unwrap();
 657     Person::delete(&conn, inserted_person.id).unwrap();
 658     Person::delete(&conn, inserted_bot.id).unwrap();
 659     Person::delete(&conn, inserted_blocked_person.id).unwrap();
 660
 661     // The with user
 662     assert_eq!(
 663       expected_post_listing_with_user,
 664       read_post_listings_with_person[0]
 665     );
 666     assert_eq!(
 667       expected_post_listing_with_user,
 668       read_post_listing_with_person
 669     );
 670
 671     // Should be only one person, IE the bot post, and blocked should be missing
 672     assert_eq!(1, read_post_listings_with_person.len());
 673
 674     // Without the user
 675     assert_eq!(
 676       expected_post_listing_no_person,
 677       read_post_listings_no_person[1]
 678     );
 679     assert_eq!(expected_post_listing_no_person, read_post_listing_no_person);
 680
 681     // Should be 2 posts, with the bot post, and the blocked
 682     assert_eq!(3, read_post_listings_no_person.len());
 683
 684     // Should be 0 posts after the community block
 685     assert_eq!(0, read_post_listings_with_person_after_block.len());
 686
 687     assert_eq!(expected_post_like, inserted_post_like);
 688     assert_eq!(1, like_removed);
 689     assert_eq!(1, num_deleted);
 690   }
 691 }