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(post_aggregates::score, post_aggregates::newest_comment_time).desc(),
 360         )
 361         .then_order_by(post_aggregates::newest_comment_time.desc()),
 362       SortType::Hot => query
 363         .then_order_by(hot_rank(post_aggregates::score, post_aggregates::published).desc())
 364         .then_order_by(post_aggregates::published.desc()),
 365       SortType::New => query.then_order_by(post_aggregates::published.desc()),
 366       SortType::MostComments => query.then_order_by(post_aggregates::comments.desc()),
 367       SortType::TopAll => query.then_order_by(post_aggregates::score.desc()),
 368       SortType::TopYear => query
 369         .filter(post::published.gt(now - 1.years()))
 370         .then_order_by(post_aggregates::score.desc()),
 371       SortType::TopMonth => query
 372         .filter(post::published.gt(now - 1.months()))
 373         .then_order_by(post_aggregates::score.desc()),
 374       SortType::TopWeek => query
 375         .filter(post::published.gt(now - 1.weeks()))
 376         .then_order_by(post_aggregates::score.desc()),
 377       SortType::TopDay => query
 378         .filter(post::published.gt(now - 1.days()))
 379         .then_order_by(post_aggregates::score.desc()),
 380     };
 381
 382     let (limit, offset) = limit_and_offset(self.page, self.limit);
 383
 384     query = query
 385       .limit(limit)
 386       .offset(offset)
 387       .filter(post::removed.eq(false))
 388       .filter(post::deleted.eq(false))
 389       .filter(community::removed.eq(false))
 390       .filter(community::deleted.eq(false));
 391
 392     debug!("Post View Query: {:?}", debug_query::<Pg, _>(&query));
 393
 394     let res = query.load::<PostViewTuple>(self.conn)?;
 395
 396     Ok(PostView::from_tuple_to_vec(res))
 397   }
 398 }
 399
 400 impl ViewToVec for PostView {
 401   type DbTuple = PostViewTuple;
 402   fn from_tuple_to_vec(items: Vec<Self::DbTuple>) -> Vec<Self> {
 403     items
 404       .iter()
 405       .map(|a| Self {
 406         post: a.0.to_owned(),
 407         creator: a.1.to_owned(),
 408         community: a.2.to_owned(),
 409         creator_banned_from_community: a.3.is_some(),
 410         counts: a.4.to_owned(),
 411         subscribed: a.5.is_some(),
 412         saved: a.6.is_some(),
 413         read: a.7.is_some(),
 414         my_vote: a.8,
 415       })
 416       .collect::<Vec<Self>>()
 417   }
 418 }
 419
 420 #[cfg(test)]
 421 mod tests {
 422   use crate::post_view::{PostQueryBuilder, PostView};
 423   use lemmy_db_queries::{
 424     aggregates::post_aggregates::PostAggregates,
 425     establish_unpooled_connection,
 426     Crud,
 427     Likeable,
 428     ListingType,
 429     SortType,
 430   };
 431   use lemmy_db_schema::source::{community::*, post::*, user::*};
 432
 433   #[test]
 434   fn test_crud() {
 435     let conn = establish_unpooled_connection();
 436
 437     let user_name = "tegan".to_string();
 438     let community_name = "test_community_3".to_string();
 439     let post_name = "test post 3".to_string();
 440
 441     let new_user = UserForm {
 442       name: user_name.to_owned(),
 443       preferred_username: None,
 444       password_encrypted: "nope".into(),
 445       email: None,
 446       matrix_user_id: None,
 447       avatar: None,
 448       banner: None,
 449       published: None,
 450       updated: None,
 451       admin: false,
 452       banned: Some(false),
 453       show_nsfw: false,
 454       theme: "browser".into(),
 455       default_sort_type: SortType::Hot as i16,
 456       default_listing_type: ListingType::Subscribed as i16,
 457       lang: "browser".into(),
 458       show_avatars: true,
 459       send_notifications_to_email: false,
 460       actor_id: None,
 461       bio: None,
 462       local: true,
 463       private_key: None,
 464       public_key: None,
 465       last_refreshed_at: None,
 466       inbox_url: None,
 467       shared_inbox_url: None,
 468     };
 469
 470     let inserted_user = User_::create(&conn, &new_user).unwrap();
 471
 472     let new_community = CommunityForm {
 473       name: community_name.to_owned(),
 474       title: "nada".to_owned(),
 475       description: None,
 476       creator_id: inserted_user.id,
 477       category_id: 1,
 478       removed: None,
 479       deleted: None,
 480       updated: None,
 481       nsfw: false,
 482       actor_id: None,
 483       local: true,
 484       private_key: None,
 485       public_key: None,
 486       last_refreshed_at: None,
 487       published: None,
 488       icon: None,
 489       banner: None,
 490       followers_url: None,
 491       inbox_url: None,
 492       shared_inbox_url: None,
 493     };
 494
 495     let inserted_community = Community::create(&conn, &new_community).unwrap();
 496
 497     let new_post = PostForm {
 498       name: post_name.to_owned(),
 499       url: None,
 500       body: None,
 501       creator_id: inserted_user.id,
 502       community_id: inserted_community.id,
 503       removed: None,
 504       deleted: None,
 505       locked: None,
 506       stickied: None,
 507       updated: None,
 508       nsfw: false,
 509       embed_title: None,
 510       embed_description: None,
 511       embed_html: None,
 512       thumbnail_url: None,
 513       ap_id: None,
 514       local: true,
 515       published: None,
 516     };
 517
 518     let inserted_post = Post::create(&conn, &new_post).unwrap();
 519
 520     let post_like_form = PostLikeForm {
 521       post_id: inserted_post.id,
 522       user_id: inserted_user.id,
 523       score: 1,
 524     };
 525
 526     let inserted_post_like = PostLike::like(&conn, &post_like_form).unwrap();
 527
 528     let expected_post_like = PostLike {
 529       id: inserted_post_like.id,
 530       post_id: inserted_post.id,
 531       user_id: inserted_user.id,
 532       published: inserted_post_like.published,
 533       score: 1,
 534     };
 535
 536     let read_post_listings_with_user = PostQueryBuilder::create(&conn)
 537       .listing_type(&ListingType::Community)
 538       .sort(&SortType::New)
 539       .community_id(inserted_community.id)
 540       .my_user_id(inserted_user.id)
 541       .list()
 542       .unwrap();
 543
 544     let read_post_listings_no_user = PostQueryBuilder::create(&conn)
 545       .listing_type(&ListingType::Community)
 546       .sort(&SortType::New)
 547       .community_id(inserted_community.id)
 548       .list()
 549       .unwrap();
 550
 551     let read_post_listing_no_user = PostView::read(&conn, inserted_post.id, None).unwrap();
 552     let read_post_listing_with_user =
 553       PostView::read(&conn, inserted_post.id, Some(inserted_user.id)).unwrap();
 554
 555     let agg = PostAggregates::read(&conn, inserted_post.id).unwrap();
 556
 557     // the non user version
 558     let expected_post_listing_no_user = PostView {
 559       post: Post {
 560         id: inserted_post.id,
 561         name: post_name,
 562         creator_id: inserted_user.id,
 563         url: None,
 564         body: None,
 565         published: inserted_post.published,
 566         updated: None,
 567         community_id: inserted_community.id,
 568         removed: false,
 569         deleted: false,
 570         locked: false,
 571         stickied: false,
 572         nsfw: false,
 573         embed_title: None,
 574         embed_description: None,
 575         embed_html: None,
 576         thumbnail_url: None,
 577         ap_id: inserted_post.ap_id.to_owned(),
 578         local: true,
 579       },
 580       my_vote: None,
 581       creator: UserSafe {
 582         id: inserted_user.id,
 583         name: user_name,
 584         preferred_username: None,
 585         published: inserted_user.published,
 586         avatar: None,
 587         actor_id: inserted_user.actor_id.to_owned(),
 588         local: true,
 589         banned: false,
 590         deleted: false,
 591         bio: None,
 592         banner: None,
 593         admin: false,
 594         updated: None,
 595         matrix_user_id: None,
 596         inbox_url: inserted_user.inbox_url.to_owned(),
 597         shared_inbox_url: None,
 598       },
 599       creator_banned_from_community: false,
 600       community: CommunitySafe {
 601         id: inserted_community.id,
 602         name: community_name,
 603         icon: None,
 604         removed: false,
 605         deleted: false,
 606         nsfw: false,
 607         actor_id: inserted_community.actor_id.to_owned(),
 608         local: true,
 609         title: "nada".to_owned(),
 610         description: None,
 611         creator_id: inserted_user.id,
 612         category_id: 1,
 613         updated: None,
 614         banner: None,
 615         published: inserted_community.published,
 616       },
 617       counts: PostAggregates {
 618         id: agg.id,
 619         post_id: inserted_post.id,
 620         comments: 0,
 621         score: 1,
 622         upvotes: 1,
 623         downvotes: 0,
 624         stickied: false,
 625         published: agg.published,
 626         newest_comment_time: inserted_post.published,
 627       },
 628       subscribed: false,
 629       read: false,
 630       saved: false,
 631     };
 632
 633     // TODO More needs to be added here
 634     let mut expected_post_listing_with_user = expected_post_listing_no_user.to_owned();
 635     expected_post_listing_with_user.my_vote = Some(1);
 636
 637     let like_removed = PostLike::remove(&conn, inserted_user.id, inserted_post.id).unwrap();
 638     let num_deleted = Post::delete(&conn, inserted_post.id).unwrap();
 639     Community::delete(&conn, inserted_community.id).unwrap();
 640     User_::delete(&conn, inserted_user.id).unwrap();
 641
 642     // The with user
 643     assert_eq!(
 644       expected_post_listing_with_user,
 645       read_post_listings_with_user[0]
 646     );
 647     assert_eq!(expected_post_listing_with_user, read_post_listing_with_user);
 648     assert_eq!(1, read_post_listings_with_user.len());
 649
 650     // Without the user
 651     assert_eq!(expected_post_listing_no_user, read_post_listings_no_user[0]);
 652     assert_eq!(expected_post_listing_no_user, read_post_listing_no_user);
 653     assert_eq!(1, read_post_listings_no_user.len());
 654
 655     // assert_eq!(expected_post, inserted_post);
 656     // assert_eq!(expected_post, updated_post);
 657     assert_eq!(expected_post_like, inserted_post_like);
 658     assert_eq!(1, like_removed);
 659     assert_eq!(1, num_deleted);
 660   }
 661 }