crates/db_views/src/post_view.rs

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