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     };
 467
 468     let inserted_user = User_::create(&conn, &new_user).unwrap();
 469
 470     let new_community = CommunityForm {
 471       name: community_name.to_owned(),
 472       title: "nada".to_owned(),
 473       description: None,
 474       creator_id: inserted_user.id,
 475       category_id: 1,
 476       removed: None,
 477       deleted: None,
 478       updated: None,
 479       nsfw: false,
 480       actor_id: None,
 481       local: true,
 482       private_key: None,
 483       public_key: None,
 484       last_refreshed_at: None,
 485       published: None,
 486       icon: None,
 487       banner: None,
 488     };
 489
 490     let inserted_community = Community::create(&conn, &new_community).unwrap();
 491
 492     let new_post = PostForm {
 493       name: post_name.to_owned(),
 494       url: None,
 495       body: None,
 496       creator_id: inserted_user.id,
 497       community_id: inserted_community.id,
 498       removed: None,
 499       deleted: None,
 500       locked: None,
 501       stickied: None,
 502       updated: None,
 503       nsfw: false,
 504       embed_title: None,
 505       embed_description: None,
 506       embed_html: None,
 507       thumbnail_url: None,
 508       ap_id: None,
 509       local: true,
 510       published: None,
 511     };
 512
 513     let inserted_post = Post::create(&conn, &new_post).unwrap();
 514
 515     let post_like_form = PostLikeForm {
 516       post_id: inserted_post.id,
 517       user_id: inserted_user.id,
 518       score: 1,
 519     };
 520
 521     let inserted_post_like = PostLike::like(&conn, &post_like_form).unwrap();
 522
 523     let expected_post_like = PostLike {
 524       id: inserted_post_like.id,
 525       post_id: inserted_post.id,
 526       user_id: inserted_user.id,
 527       published: inserted_post_like.published,
 528       score: 1,
 529     };
 530
 531     let read_post_listings_with_user = PostQueryBuilder::create(&conn)
 532       .listing_type(&ListingType::Community)
 533       .sort(&SortType::New)
 534       .community_id(inserted_community.id)
 535       .my_user_id(inserted_user.id)
 536       .list()
 537       .unwrap();
 538
 539     let read_post_listings_no_user = PostQueryBuilder::create(&conn)
 540       .listing_type(&ListingType::Community)
 541       .sort(&SortType::New)
 542       .community_id(inserted_community.id)
 543       .list()
 544       .unwrap();
 545
 546     let read_post_listing_no_user = PostView::read(&conn, inserted_post.id, None).unwrap();
 547     let read_post_listing_with_user =
 548       PostView::read(&conn, inserted_post.id, Some(inserted_user.id)).unwrap();
 549
 550     let agg = PostAggregates::read(&conn, inserted_post.id).unwrap();
 551
 552     // the non user version
 553     let expected_post_listing_no_user = PostView {
 554       post: Post {
 555         id: inserted_post.id,
 556         name: post_name,
 557         creator_id: inserted_user.id,
 558         url: None,
 559         body: None,
 560         published: inserted_post.published,
 561         updated: None,
 562         community_id: inserted_community.id,
 563         removed: false,
 564         deleted: false,
 565         locked: false,
 566         stickied: false,
 567         nsfw: false,
 568         embed_title: None,
 569         embed_description: None,
 570         embed_html: None,
 571         thumbnail_url: None,
 572         ap_id: inserted_post.ap_id.to_owned(),
 573         local: true,
 574       },
 575       my_vote: None,
 576       creator: UserSafe {
 577         id: inserted_user.id,
 578         name: user_name,
 579         preferred_username: None,
 580         published: inserted_user.published,
 581         avatar: None,
 582         actor_id: inserted_user.actor_id.to_owned(),
 583         local: true,
 584         banned: false,
 585         deleted: false,
 586         bio: None,
 587         banner: None,
 588         admin: false,
 589         updated: None,
 590         matrix_user_id: None,
 591       },
 592       creator_banned_from_community: false,
 593       community: CommunitySafe {
 594         id: inserted_community.id,
 595         name: community_name,
 596         icon: None,
 597         removed: false,
 598         deleted: false,
 599         nsfw: false,
 600         actor_id: inserted_community.actor_id.to_owned(),
 601         local: true,
 602         title: "nada".to_owned(),
 603         description: None,
 604         creator_id: inserted_user.id,
 605         category_id: 1,
 606         updated: None,
 607         banner: None,
 608         published: inserted_community.published,
 609       },
 610       counts: PostAggregates {
 611         id: agg.id,
 612         post_id: inserted_post.id,
 613         comments: 0,
 614         score: 1,
 615         upvotes: 1,
 616         downvotes: 0,
 617         stickied: false,
 618         published: agg.published,
 619         newest_comment_time: inserted_post.published,
 620       },
 621       subscribed: false,
 622       read: false,
 623       saved: false,
 624     };
 625
 626     // TODO More needs to be added here
 627     let mut expected_post_listing_with_user = expected_post_listing_no_user.to_owned();
 628     expected_post_listing_with_user.my_vote = Some(1);
 629
 630     let like_removed = PostLike::remove(&conn, inserted_user.id, inserted_post.id).unwrap();
 631     let num_deleted = Post::delete(&conn, inserted_post.id).unwrap();
 632     Community::delete(&conn, inserted_community.id).unwrap();
 633     User_::delete(&conn, inserted_user.id).unwrap();
 634
 635     // The with user
 636     assert_eq!(
 637       expected_post_listing_with_user,
 638       read_post_listings_with_user[0]
 639     );
 640     assert_eq!(expected_post_listing_with_user, read_post_listing_with_user);
 641     assert_eq!(1, read_post_listings_with_user.len());
 642
 643     // Without the user
 644     assert_eq!(expected_post_listing_no_user, read_post_listings_no_user[0]);
 645     assert_eq!(expected_post_listing_no_user, read_post_listing_no_user);
 646     assert_eq!(1, read_post_listings_no_user.len());
 647
 648     // assert_eq!(expected_post, inserted_post);
 649     // assert_eq!(expected_post, updated_post);
 650     assert_eq!(expected_post_like, inserted_post_like);
 651     assert_eq!(1, like_removed);
 652     assert_eq!(1, num_deleted);
 653   }
 654 }