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::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.filter(community_id.eq(for_community_id));
 276       query = query.then_order_by(stickied.desc());
 277     }
 278
 279     if let Some(for_community_name) = self.for_community_name {
 280       query = query.filter(community_name.eq(for_community_name));
 281       query = query.then_order_by(stickied.desc());
 282     }
 283
 284     if let Some(url_search) = self.url_search {
 285       query = query.filter(url.eq(url_search));
 286     }
 287
 288     if let Some(search_term) = self.search_term {
 289       let searcher = fuzzy_search(&search_term);
 290       query = query
 291         .filter(name.ilike(searcher.to_owned()))
 292         .or_filter(body.ilike(searcher));
 293     }
 294
 295     query = match self.sort {
 296       SortType::Active => query
 297         .then_order_by(hot_rank_active.desc())
 298         .then_order_by(published.desc()),
 299       SortType::Hot => query
 300         .then_order_by(hot_rank.desc())
 301         .then_order_by(published.desc()),
 302       SortType::New => query.then_order_by(published.desc()),
 303       SortType::TopAll => query.then_order_by(score.desc()),
 304       SortType::TopYear => query
 305         .filter(published.gt(now - 1.years()))
 306         .then_order_by(score.desc()),
 307       SortType::TopMonth => query
 308         .filter(published.gt(now - 1.months()))
 309         .then_order_by(score.desc()),
 310       SortType::TopWeek => query
 311         .filter(published.gt(now - 1.weeks()))
 312         .then_order_by(score.desc()),
 313       SortType::TopDay => query
 314         .filter(published.gt(now - 1.days()))
 315         .then_order_by(score.desc()),
 316     };
 317
 318     // The view lets you pass a null user_id, if you're not logged in
 319     query = if let Some(my_user_id) = self.my_user_id {
 320       query.filter(user_id.eq(my_user_id))
 321     } else {
 322       query.filter(user_id.is_null())
 323     };
 324
 325     // If its for a specific user, show the removed / deleted
 326     if let Some(for_creator_id) = self.for_creator_id {
 327       query = query.filter(creator_id.eq(for_creator_id));
 328     } else {
 329       query = query
 330         .filter(removed.eq(false))
 331         .filter(deleted.eq(false))
 332         .filter(community_removed.eq(false))
 333         .filter(community_deleted.eq(false));
 334     }
 335
 336     if !self.show_nsfw {
 337       query = query
 338         .filter(nsfw.eq(false))
 339         .filter(community_nsfw.eq(false));
 340     };
 341
 342     // TODO these are wrong, bc they'll only show saved for your logged in user, not theirs
 343     if self.saved_only {
 344       query = query.filter(saved.eq(true));
 345     };
 346
 347     if self.unread_only {
 348       query = query.filter(read.eq(false));
 349     };
 350
 351     let (limit, offset) = limit_and_offset(self.page, self.limit);
 352     query = query
 353       .limit(limit)
 354       .offset(offset)
 355       .filter(removed.eq(false))
 356       .filter(deleted.eq(false))
 357       .filter(community_removed.eq(false))
 358       .filter(community_deleted.eq(false));
 359
 360     query.load::<PostView>(self.conn)
 361   }
 362 }
 363
 364 impl PostView {
 365   pub fn read(
 366     conn: &PgConnection,
 367     from_post_id: i32,
 368     my_user_id: Option<i32>,
 369   ) -> Result<Self, Error> {
 370     use super::post_view::post_fast_view::dsl::*;
 371     use diesel::prelude::*;
 372
 373     let mut query = post_fast_view.into_boxed();
 374
 375     query = query.filter(id.eq(from_post_id));
 376
 377     if let Some(my_user_id) = my_user_id {
 378       query = query.filter(user_id.eq(my_user_id));
 379     } else {
 380       query = query.filter(user_id.is_null());
 381     };
 382
 383     query.first::<Self>(conn)
 384   }
 385 }
 386
 387 #[cfg(test)]
 388 mod tests {
 389   use crate::{
 390     community::*,
 391     post::*,
 392     post_view::*,
 393     tests::establish_unpooled_connection,
 394     user::*,
 395     Crud,
 396     Likeable,
 397     *,
 398   };
 399
 400   #[test]
 401   fn test_crud() {
 402     let conn = establish_unpooled_connection();
 403
 404     let user_name = "tegan".to_string();
 405     let community_name = "test_community_3".to_string();
 406     let post_name = "test post 3".to_string();
 407
 408     let new_user = UserForm {
 409       name: user_name.to_owned(),
 410       preferred_username: None,
 411       password_encrypted: "nope".into(),
 412       email: None,
 413       matrix_user_id: None,
 414       avatar: None,
 415       banner: None,
 416       updated: None,
 417       admin: false,
 418       banned: false,
 419       show_nsfw: false,
 420       theme: "darkly".into(),
 421       default_sort_type: SortType::Hot as i16,
 422       default_listing_type: ListingType::Subscribed as i16,
 423       lang: "browser".into(),
 424       show_avatars: true,
 425       send_notifications_to_email: false,
 426       actor_id: None,
 427       bio: None,
 428       local: true,
 429       private_key: None,
 430       public_key: None,
 431       last_refreshed_at: None,
 432     };
 433
 434     let inserted_user = User_::create(&conn, &new_user).unwrap();
 435
 436     let new_community = CommunityForm {
 437       name: community_name.to_owned(),
 438       title: "nada".to_owned(),
 439       description: None,
 440       creator_id: inserted_user.id,
 441       category_id: 1,
 442       removed: None,
 443       deleted: None,
 444       updated: None,
 445       nsfw: false,
 446       actor_id: None,
 447       local: true,
 448       private_key: None,
 449       public_key: None,
 450       last_refreshed_at: None,
 451       published: None,
 452       icon: None,
 453       banner: None,
 454     };
 455
 456     let inserted_community = Community::create(&conn, &new_community).unwrap();
 457
 458     let new_post = PostForm {
 459       name: post_name.to_owned(),
 460       url: None,
 461       body: None,
 462       creator_id: inserted_user.id,
 463       community_id: inserted_community.id,
 464       removed: None,
 465       deleted: None,
 466       locked: None,
 467       stickied: None,
 468       updated: None,
 469       nsfw: false,
 470       embed_title: None,
 471       embed_description: None,
 472       embed_html: None,
 473       thumbnail_url: None,
 474       ap_id: None,
 475       local: true,
 476       published: None,
 477     };
 478
 479     let inserted_post = Post::create(&conn, &new_post).unwrap();
 480
 481     let post_like_form = PostLikeForm {
 482       post_id: inserted_post.id,
 483       user_id: inserted_user.id,
 484       score: 1,
 485     };
 486
 487     let inserted_post_like = PostLike::like(&conn, &post_like_form).unwrap();
 488
 489     let expected_post_like = PostLike {
 490       id: inserted_post_like.id,
 491       post_id: inserted_post.id,
 492       user_id: inserted_user.id,
 493       published: inserted_post_like.published,
 494       score: 1,
 495     };
 496
 497     let read_post_listings_with_user = PostQueryBuilder::create(&conn)
 498       .listing_type(ListingType::Community)
 499       .sort(&SortType::New)
 500       .for_community_id(inserted_community.id)
 501       .my_user_id(inserted_user.id)
 502       .list()
 503       .unwrap();
 504
 505     let read_post_listings_no_user = PostQueryBuilder::create(&conn)
 506       .listing_type(ListingType::Community)
 507       .sort(&SortType::New)
 508       .for_community_id(inserted_community.id)
 509       .list()
 510       .unwrap();
 511
 512     let read_post_listing_no_user = PostView::read(&conn, inserted_post.id, None).unwrap();
 513     let read_post_listing_with_user =
 514       PostView::read(&conn, inserted_post.id, Some(inserted_user.id)).unwrap();
 515
 516     // the non user version
 517     let expected_post_listing_no_user = PostView {
 518       user_id: None,
 519       my_vote: None,
 520       id: inserted_post.id,
 521       name: post_name.to_owned(),
 522       url: None,
 523       body: None,
 524       creator_id: inserted_user.id,
 525       creator_name: user_name.to_owned(),
 526       creator_preferred_username: None,
 527       creator_published: inserted_user.published,
 528       creator_avatar: None,
 529       banned: false,
 530       banned_from_community: false,
 531       community_id: inserted_community.id,
 532       removed: false,
 533       deleted: false,
 534       locked: false,
 535       stickied: false,
 536       community_name: community_name.to_owned(),
 537       community_icon: None,
 538       community_removed: false,
 539       community_deleted: false,
 540       community_nsfw: false,
 541       number_of_comments: 0,
 542       score: 1,
 543       upvotes: 1,
 544       downvotes: 0,
 545       hot_rank: read_post_listing_no_user.hot_rank,
 546       hot_rank_active: read_post_listing_no_user.hot_rank_active,
 547       published: inserted_post.published,
 548       newest_activity_time: inserted_post.published,
 549       updated: None,
 550       subscribed: None,
 551       read: None,
 552       saved: None,
 553       nsfw: false,
 554       embed_title: None,
 555       embed_description: None,
 556       embed_html: None,
 557       thumbnail_url: None,
 558       ap_id: inserted_post.ap_id.to_owned(),
 559       local: true,
 560       creator_actor_id: inserted_user.actor_id.to_owned(),
 561       creator_local: true,
 562       community_actor_id: inserted_community.actor_id.to_owned(),
 563       community_local: true,
 564     };
 565
 566     let expected_post_listing_with_user = PostView {
 567       user_id: Some(inserted_user.id),
 568       my_vote: Some(1),
 569       id: inserted_post.id,
 570       name: post_name,
 571       url: None,
 572       body: None,
 573       removed: false,
 574       deleted: false,
 575       locked: false,
 576       stickied: false,
 577       creator_id: inserted_user.id,
 578       creator_name: user_name,
 579       creator_preferred_username: None,
 580       creator_published: inserted_user.published,
 581       creator_avatar: None,
 582       banned: false,
 583       banned_from_community: false,
 584       community_id: inserted_community.id,
 585       community_name,
 586       community_icon: None,
 587       community_removed: false,
 588       community_deleted: false,
 589       community_nsfw: false,
 590       number_of_comments: 0,
 591       score: 1,
 592       upvotes: 1,
 593       downvotes: 0,
 594       hot_rank: read_post_listing_with_user.hot_rank,
 595       hot_rank_active: read_post_listing_with_user.hot_rank_active,
 596       published: inserted_post.published,
 597       newest_activity_time: inserted_post.published,
 598       updated: None,
 599       subscribed: Some(false),
 600       read: Some(false),
 601       saved: Some(false),
 602       nsfw: false,
 603       embed_title: None,
 604       embed_description: None,
 605       embed_html: None,
 606       thumbnail_url: None,
 607       ap_id: inserted_post.ap_id.to_owned(),
 608       local: true,
 609       creator_actor_id: inserted_user.actor_id.to_owned(),
 610       creator_local: true,
 611       community_actor_id: inserted_community.actor_id.to_owned(),
 612       community_local: true,
 613     };
 614
 615     let like_removed = PostLike::remove(&conn, inserted_user.id, inserted_post.id).unwrap();
 616     let num_deleted = Post::delete(&conn, inserted_post.id).unwrap();
 617     Community::delete(&conn, inserted_community.id).unwrap();
 618     User_::delete(&conn, inserted_user.id).unwrap();
 619
 620     // The with user
 621     assert_eq!(
 622       expected_post_listing_with_user,
 623       read_post_listings_with_user[0]
 624     );
 625     assert_eq!(expected_post_listing_with_user, read_post_listing_with_user);
 626     assert_eq!(1, read_post_listings_with_user.len());
 627
 628     // Without the user
 629     assert_eq!(expected_post_listing_no_user, read_post_listings_no_user[0]);
 630     assert_eq!(expected_post_listing_no_user, read_post_listing_no_user);
 631     assert_eq!(1, read_post_listings_no_user.len());
 632
 633     // assert_eq!(expected_post, inserted_post);
 634     // assert_eq!(expected_post, updated_post);
 635     assert_eq!(expected_post_like, inserted_post_like);
 636     assert_eq!(1, like_removed);
 637     assert_eq!(1, num_deleted);
 638   }
 639 }