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