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