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