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