server/lemmy_db/src/post_view.rs

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