server/src/db/post_view.rs

   1 use super::*;
   2
   3 #[derive(EnumString, ToString, Debug, Serialize, Deserialize)]
   4 pub enum PostListingType {
   5   All,
   6   Subscribed,
   7   Community,
   8 }
   9
  10 // The faked schema since diesel doesn't do views
  11 table! {
  12   post_view (id) {
  13     id -> Int4,
  14     name -> Varchar,
  15     url -> Nullable<Text>,
  16     body -> Nullable<Text>,
  17     creator_id -> Int4,
  18     community_id -> Int4,
  19     removed -> Bool,
  20     locked -> Bool,
  21     published -> Timestamp,
  22     updated -> Nullable<Timestamp>,
  23     deleted -> Bool,
  24     nsfw -> Bool,
  25     banned -> Bool,
  26     banned_from_community -> Bool,
  27     stickied -> Bool,
  28     creator_name -> Varchar,
  29     community_name -> Varchar,
  30     community_removed -> Bool,
  31     community_deleted -> Bool,
  32     community_nsfw -> Bool,
  33     number_of_comments -> BigInt,
  34     score -> BigInt,
  35     upvotes -> BigInt,
  36     downvotes -> BigInt,
  37     hot_rank -> Int4,
  38     user_id -> Nullable<Int4>,
  39     my_vote -> Nullable<Int4>,
  40     subscribed -> Nullable<Bool>,
  41     read -> Nullable<Bool>,
  42     saved -> Nullable<Bool>,
  43   }
  44 }
  45
  46 #[derive(
  47   Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
  48 )]
  49 #[table_name = "post_view"]
  50 pub struct PostView {
  51   pub id: i32,
  52   pub name: String,
  53   pub url: Option<String>,
  54   pub body: Option<String>,
  55   pub creator_id: i32,
  56   pub community_id: i32,
  57   pub removed: bool,
  58   pub locked: bool,
  59   pub published: chrono::NaiveDateTime,
  60   pub updated: Option<chrono::NaiveDateTime>,
  61   pub deleted: bool,
  62   pub nsfw: bool,
  63   pub banned: bool,
  64   pub banned_from_community: bool,
  65   pub stickied: bool,
  66   pub creator_name: String,
  67   pub community_name: String,
  68   pub community_removed: bool,
  69   pub community_deleted: bool,
  70   pub community_nsfw: bool,
  71   pub number_of_comments: i64,
  72   pub score: i64,
  73   pub upvotes: i64,
  74   pub downvotes: i64,
  75   pub hot_rank: i32,
  76   pub user_id: Option<i32>,
  77   pub my_vote: Option<i32>,
  78   pub subscribed: Option<bool>,
  79   pub read: Option<bool>,
  80   pub saved: Option<bool>,
  81 }
  82
  83 impl PostView {
  84   pub fn list(
  85     conn: &PgConnection,
  86     type_: PostListingType,
  87     sort: &SortType,
  88     for_community_id: Option<i32>,
  89     for_creator_id: Option<i32>,
  90     search_term: Option<String>,
  91     url_search: Option<String>,
  92     my_user_id: Option<i32>,
  93     show_nsfw: bool,
  94     saved_only: bool,
  95     unread_only: bool,
  96     page: Option<i64>,
  97     limit: Option<i64>,
  98   ) -> Result<Vec<Self>, Error> {
  99     use super::post_view::post_view::dsl::*;
 100
 101     let (limit, offset) = limit_and_offset(page, limit);
 102
 103     let mut query = post_view.into_boxed();
 104
 105     if let Some(for_creator_id) = for_creator_id {
 106       query = query.filter(creator_id.eq(for_creator_id));
 107     };
 108
 109     if let Some(search_term) = search_term {
 110       query = query.filter(name.ilike(fuzzy_search(&search_term)));
 111     };
 112
 113     if let Some(url_search) = url_search {
 114       query = query.filter(url.eq(url_search));
 115     };
 116
 117     if let Some(for_community_id) = for_community_id {
 118       query = query.filter(community_id.eq(for_community_id));
 119       query = query.then_order_by(stickied.desc());
 120     };
 121
 122     // TODO these are wrong, bc they'll only show saved for your logged in user, not theirs
 123     if saved_only {
 124       query = query.filter(saved.eq(true));
 125     };
 126
 127     if unread_only {
 128       query = query.filter(read.eq(false));
 129     };
 130
 131     match type_ {
 132       PostListingType::Subscribed => {
 133         query = query.filter(subscribed.eq(true));
 134       }
 135       _ => {}
 136     };
 137
 138     // The view lets you pass a null user_id, if you're not logged in
 139     if let Some(my_user_id) = my_user_id {
 140       query = query.filter(user_id.eq(my_user_id));
 141     } else {
 142       query = query.filter(user_id.is_null());
 143     }
 144
 145     if !show_nsfw {
 146       query = query
 147         .filter(nsfw.eq(false))
 148         .filter(community_nsfw.eq(false));
 149     };
 150
 151     query = match sort {
 152       SortType::Hot => query
 153         .then_order_by(hot_rank.desc())
 154         .then_order_by(published.desc()),
 155       SortType::New => query.then_order_by(published.desc()),
 156       SortType::TopAll => query.then_order_by(score.desc()),
 157       SortType::TopYear => query
 158         .filter(published.gt(now - 1.years()))
 159         .then_order_by(score.desc()),
 160       SortType::TopMonth => query
 161         .filter(published.gt(now - 1.months()))
 162         .then_order_by(score.desc()),
 163       SortType::TopWeek => query
 164         .filter(published.gt(now - 1.weeks()))
 165         .then_order_by(score.desc()),
 166       SortType::TopDay => query
 167         .filter(published.gt(now - 1.days()))
 168         .then_order_by(score.desc()),
 169     };
 170
 171     query = query
 172       .limit(limit)
 173       .offset(offset)
 174       .filter(removed.eq(false))
 175       .filter(deleted.eq(false))
 176       .filter(community_removed.eq(false))
 177       .filter(community_deleted.eq(false));
 178
 179     query.load::<Self>(conn)
 180   }
 181
 182   pub fn read(
 183     conn: &PgConnection,
 184     from_post_id: i32,
 185     my_user_id: Option<i32>,
 186   ) -> Result<Self, Error> {
 187     use super::post_view::post_view::dsl::*;
 188     use diesel::prelude::*;
 189
 190     let mut query = post_view.into_boxed();
 191
 192     query = query.filter(id.eq(from_post_id));
 193
 194     if let Some(my_user_id) = my_user_id {
 195       query = query.filter(user_id.eq(my_user_id));
 196     } else {
 197       query = query.filter(user_id.is_null());
 198     };
 199
 200     query.first::<Self>(conn)
 201   }
 202 }
 203
 204 #[cfg(test)]
 205 mod tests {
 206   use super::super::community::*;
 207   use super::super::post::*;
 208   use super::super::user::*;
 209   use super::*;
 210   #[test]
 211   fn test_crud() {
 212     let conn = establish_connection();
 213
 214     let user_name = "tegan".to_string();
 215     let community_name = "test_community_3".to_string();
 216     let post_name = "test post 3".to_string();
 217
 218     let new_user = UserForm {
 219       name: user_name.to_owned(),
 220       fedi_name: "rrf".into(),
 221       preferred_username: None,
 222       password_encrypted: "nope".into(),
 223       email: None,
 224       updated: None,
 225       admin: false,
 226       banned: false,
 227       show_nsfw: false,
 228       theme: "darkly".into(),
 229     };
 230
 231     let inserted_user = User_::create(&conn, &new_user).unwrap();
 232
 233     let new_community = CommunityForm {
 234       name: community_name.to_owned(),
 235       title: "nada".to_owned(),
 236       description: None,
 237       creator_id: inserted_user.id,
 238       category_id: 1,
 239       removed: None,
 240       deleted: None,
 241       updated: None,
 242       nsfw: false,
 243     };
 244
 245     let inserted_community = Community::create(&conn, &new_community).unwrap();
 246
 247     let new_post = PostForm {
 248       name: post_name.to_owned(),
 249       url: None,
 250       body: None,
 251       creator_id: inserted_user.id,
 252       community_id: inserted_community.id,
 253       removed: None,
 254       deleted: None,
 255       locked: None,
 256       stickied: None,
 257       updated: None,
 258       nsfw: false,
 259     };
 260
 261     let inserted_post = Post::create(&conn, &new_post).unwrap();
 262
 263     let post_like_form = PostLikeForm {
 264       post_id: inserted_post.id,
 265       user_id: inserted_user.id,
 266       score: 1,
 267     };
 268
 269     let inserted_post_like = PostLike::like(&conn, &post_like_form).unwrap();
 270
 271     let expected_post_like = PostLike {
 272       id: inserted_post_like.id,
 273       post_id: inserted_post.id,
 274       user_id: inserted_user.id,
 275       published: inserted_post_like.published,
 276       score: 1,
 277     };
 278
 279     let post_like_form = PostLikeForm {
 280       post_id: inserted_post.id,
 281       user_id: inserted_user.id,
 282       score: 1,
 283     };
 284
 285     // the non user version
 286     let expected_post_listing_no_user = PostView {
 287       user_id: None,
 288       my_vote: None,
 289       id: inserted_post.id,
 290       name: post_name.to_owned(),
 291       url: None,
 292       body: None,
 293       creator_id: inserted_user.id,
 294       creator_name: user_name.to_owned(),
 295       banned: false,
 296       banned_from_community: false,
 297       community_id: inserted_community.id,
 298       removed: false,
 299       deleted: false,
 300       locked: false,
 301       stickied: false,
 302       community_name: community_name.to_owned(),
 303       community_removed: false,
 304       community_deleted: false,
 305       community_nsfw: false,
 306       number_of_comments: 0,
 307       score: 1,
 308       upvotes: 1,
 309       downvotes: 0,
 310       hot_rank: 1728,
 311       published: inserted_post.published,
 312       updated: None,
 313       subscribed: None,
 314       read: None,
 315       saved: None,
 316       nsfw: false,
 317     };
 318
 319     let expected_post_listing_with_user = PostView {
 320       user_id: Some(inserted_user.id),
 321       my_vote: Some(1),
 322       id: inserted_post.id,
 323       name: post_name.to_owned(),
 324       url: None,
 325       body: None,
 326       removed: false,
 327       deleted: false,
 328       locked: false,
 329       stickied: false,
 330       creator_id: inserted_user.id,
 331       creator_name: user_name.to_owned(),
 332       banned: false,
 333       banned_from_community: false,
 334       community_id: inserted_community.id,
 335       community_name: community_name.to_owned(),
 336       community_removed: false,
 337       community_deleted: false,
 338       community_nsfw: false,
 339       number_of_comments: 0,
 340       score: 1,
 341       upvotes: 1,
 342       downvotes: 0,
 343       hot_rank: 1728,
 344       published: inserted_post.published,
 345       updated: None,
 346       subscribed: None,
 347       read: None,
 348       saved: None,
 349       nsfw: false,
 350     };
 351
 352     let read_post_listings_with_user = PostView::list(
 353       &conn,
 354       PostListingType::Community,
 355       &SortType::New,
 356       Some(inserted_community.id),
 357       None,
 358       None,
 359       None,
 360       Some(inserted_user.id),
 361       false,
 362       false,
 363       false,
 364       None,
 365       None,
 366     )
 367     .unwrap();
 368     let read_post_listings_no_user = PostView::list(
 369       &conn,
 370       PostListingType::Community,
 371       &SortType::New,
 372       Some(inserted_community.id),
 373       None,
 374       None,
 375       None,
 376       None,
 377       false,
 378       false,
 379       false,
 380       None,
 381       None,
 382     )
 383     .unwrap();
 384     let read_post_listing_no_user = PostView::read(&conn, inserted_post.id, None).unwrap();
 385     let read_post_listing_with_user =
 386       PostView::read(&conn, inserted_post.id, Some(inserted_user.id)).unwrap();
 387
 388     let like_removed = PostLike::remove(&conn, &post_like_form).unwrap();
 389     let num_deleted = Post::delete(&conn, inserted_post.id).unwrap();
 390     Community::delete(&conn, inserted_community.id).unwrap();
 391     User_::delete(&conn, inserted_user.id).unwrap();
 392
 393     // The with user
 394     assert_eq!(
 395       expected_post_listing_with_user,
 396       read_post_listings_with_user[0]
 397     );
 398     assert_eq!(expected_post_listing_with_user, read_post_listing_with_user);
 399     assert_eq!(1, read_post_listings_with_user.len());
 400
 401     // Without the user
 402     assert_eq!(expected_post_listing_no_user, read_post_listings_no_user[0]);
 403     assert_eq!(expected_post_listing_no_user, read_post_listing_no_user);
 404     assert_eq!(1, read_post_listings_no_user.len());
 405
 406     // assert_eq!(expected_post, inserted_post);
 407     // assert_eq!(expected_post, updated_post);
 408     assert_eq!(expected_post_like, inserted_post_like);
 409     assert_eq!(1, like_removed);
 410     assert_eq!(1, num_deleted);
 411   }
 412 }