lemmy_db/src/views/post_view.rs

   1 use crate::{
   2   aggregates::post_aggregates::PostAggregates,
   3   community::{Community, CommunityFollower, CommunitySafe, CommunityUserBan},
   4   functions::hot_rank,
   5   fuzzy_search,
   6   limit_and_offset,
   7   post::{Post, PostRead, PostSaved},
   8   schema::{
   9     community,
  10     community_follower,
  11     community_user_ban,
  12     post,
  13     post_aggregates,
  14     post_like,
  15     post_read,
  16     post_saved,
  17     user_,
  18   },
  19   user::{UserSafe, User_},
  20   ListingType,
  21   MaybeOptional,
  22   SortType,
  23   ToSafe,
  24 };
  25 use diesel::{result::Error, *};
  26 use serde::Serialize;
  27
  28 #[derive(Debug, Serialize, Clone)]
  29 pub struct PostView {
  30   pub post: Post,
  31   pub creator: UserSafe,
  32   pub community: CommunitySafe,
  33   pub counts: PostAggregates,
  34   pub subscribed: bool,            // Left join to CommunityFollower
  35   pub banned_from_community: bool, // Left Join to CommunityUserBan
  36   pub saved: bool,                 // Left join to PostSaved
  37   pub read: bool,                  // Left join to PostRead
  38   pub my_vote: Option<i16>,        // Left join to PostLike
  39 }
  40
  41 type OutputTuple = (
  42   Post,
  43   UserSafe,
  44   CommunitySafe,
  45   PostAggregates,
  46   Option<CommunityFollower>,
  47   Option<CommunityUserBan>,
  48   Option<PostSaved>,
  49   Option<PostRead>,
  50   Option<i16>,
  51 );
  52
  53 impl PostView {
  54   pub fn read(conn: &PgConnection, post_id: i32, my_user_id: Option<i32>) -> Result<Self, Error> {
  55     // The left join below will return None in this case
  56     let user_id_join = my_user_id.unwrap_or(-1);
  57
  58     let (post, creator, community, counts, follower, banned_from_community, saved, read, my_vote) =
  59       post::table
  60         .find(post_id)
  61         .inner_join(user_::table)
  62         .inner_join(community::table)
  63         .inner_join(post_aggregates::table)
  64         .left_join(
  65           community_follower::table.on(
  66             post::community_id
  67               .eq(community_follower::community_id)
  68               .and(community_follower::user_id.eq(user_id_join)),
  69           ),
  70         )
  71         .left_join(
  72           community_user_ban::table.on(
  73             post::community_id
  74               .eq(community_user_ban::community_id)
  75               .and(community_user_ban::user_id.eq(user_id_join)),
  76           ),
  77         )
  78         .left_join(
  79           post_saved::table.on(
  80             post::id
  81               .eq(post_saved::post_id)
  82               .and(post_saved::user_id.eq(user_id_join)),
  83           ),
  84         )
  85         .left_join(
  86           post_read::table.on(
  87             post::id
  88               .eq(post_read::post_id)
  89               .and(post_read::user_id.eq(user_id_join)),
  90           ),
  91         )
  92         .left_join(
  93           post_like::table.on(
  94             post::id
  95               .eq(post_like::post_id)
  96               .and(post_like::user_id.eq(user_id_join)),
  97           ),
  98         )
  99         .select((
 100           post::all_columns,
 101           User_::safe_columns_tuple(),
 102           Community::safe_columns_tuple(),
 103           post_aggregates::all_columns,
 104           community_follower::all_columns.nullable(),
 105           community_user_ban::all_columns.nullable(),
 106           post_saved::all_columns.nullable(),
 107           post_read::all_columns.nullable(),
 108           post_like::score.nullable(),
 109         ))
 110         .first::<OutputTuple>(conn)?;
 111
 112     Ok(PostView {
 113       post,
 114       creator,
 115       community,
 116       counts,
 117       subscribed: follower.is_some(),
 118       banned_from_community: banned_from_community.is_some(),
 119       saved: saved.is_some(),
 120       read: read.is_some(),
 121       my_vote,
 122     })
 123   }
 124 }
 125
 126 mod join_types {
 127   use crate::schema::{
 128     community,
 129     community_follower,
 130     community_user_ban,
 131     post,
 132     post_aggregates,
 133     post_like,
 134     post_read,
 135     post_saved,
 136     user_,
 137   };
 138   use diesel::{
 139     pg::Pg,
 140     query_builder::BoxedSelectStatement,
 141     query_source::joins::{Inner, Join, JoinOn, LeftOuter},
 142     sql_types::*,
 143   };
 144
 145   /// TODO awful, but necessary because of the boxed join
 146   pub(super) type BoxedPostJoin<'a> = BoxedSelectStatement<
 147     'a,
 148     (
 149       (
 150         Integer,
 151         Text,
 152         Nullable<Text>,
 153         Nullable<Text>,
 154         Integer,
 155         Integer,
 156         Bool,
 157         Bool,
 158         Timestamp,
 159         Nullable<Timestamp>,
 160         Bool,
 161         Bool,
 162         Bool,
 163         Nullable<Text>,
 164         Nullable<Text>,
 165         Nullable<Text>,
 166         Nullable<Text>,
 167         Text,
 168         Bool,
 169       ),
 170       (
 171         Integer,
 172         Text,
 173         Nullable<Text>,
 174         Nullable<Text>,
 175         Bool,
 176         Bool,
 177         Timestamp,
 178         Nullable<Timestamp>,
 179         Nullable<Text>,
 180         Text,
 181         Nullable<Text>,
 182         Bool,
 183         Nullable<Text>,
 184         Bool,
 185       ),
 186       (
 187         Integer,
 188         Text,
 189         Text,
 190         Nullable<Text>,
 191         Integer,
 192         Integer,
 193         Bool,
 194         Timestamp,
 195         Nullable<Timestamp>,
 196         Bool,
 197         Bool,
 198         Text,
 199         Bool,
 200         Nullable<Text>,
 201         Nullable<Text>,
 202       ),
 203       (Integer, Integer, BigInt, BigInt, BigInt, BigInt, Timestamp),
 204       Nullable<(Integer, Integer, Integer, Timestamp, Nullable<Bool>)>,
 205       Nullable<(Integer, Integer, Integer, Timestamp)>,
 206       Nullable<(Integer, Integer, Integer, Timestamp)>,
 207       Nullable<(Integer, Integer, Integer, Timestamp)>,
 208       Nullable<SmallInt>,
 209     ),
 210     JoinOn<
 211       Join<
 212         JoinOn<
 213           Join<
 214             JoinOn<
 215               Join<
 216                 JoinOn<
 217                   Join<
 218                     JoinOn<
 219                       Join<
 220                         JoinOn<
 221                           Join<
 222                             JoinOn<
 223                               Join<
 224                                 JoinOn<
 225                                   Join<post::table, user_::table, Inner>,
 226                                   diesel::expression::operators::Eq<
 227                                     diesel::expression::nullable::Nullable<
 228                                       post::columns::creator_id,
 229                                     >,
 230                                     diesel::expression::nullable::Nullable<user_::columns::id>,
 231                                   >,
 232                                 >,
 233                                 community::table,
 234                                 Inner,
 235                               >,
 236                               diesel::expression::operators::Eq<
 237                                 diesel::expression::nullable::Nullable<post::columns::community_id>,
 238                                 diesel::expression::nullable::Nullable<community::columns::id>,
 239                               >,
 240                             >,
 241                             post_aggregates::table,
 242                             Inner,
 243                           >,
 244                           diesel::expression::operators::Eq<
 245                             diesel::expression::nullable::Nullable<
 246                               post_aggregates::columns::post_id,
 247                             >,
 248                             diesel::expression::nullable::Nullable<post::columns::id>,
 249                           >,
 250                         >,
 251                         community_follower::table,
 252                         LeftOuter,
 253                       >,
 254                       diesel::expression::operators::And<
 255                         diesel::expression::operators::Eq<
 256                           post::columns::community_id,
 257                           community_follower::columns::community_id,
 258                         >,
 259                         diesel::expression::operators::Eq<
 260                           community_follower::columns::user_id,
 261                           diesel::expression::bound::Bound<diesel::sql_types::Integer, i32>,
 262                         >,
 263                       >,
 264                     >,
 265                     community_user_ban::table,
 266                     LeftOuter,
 267                   >,
 268                   diesel::expression::operators::And<
 269                     diesel::expression::operators::Eq<
 270                       post::columns::community_id,
 271                       community_user_ban::columns::community_id,
 272                     >,
 273                     diesel::expression::operators::Eq<
 274                       community_user_ban::columns::user_id,
 275                       diesel::expression::bound::Bound<diesel::sql_types::Integer, i32>,
 276                     >,
 277                   >,
 278                 >,
 279                 post_saved::table,
 280                 LeftOuter,
 281               >,
 282               diesel::expression::operators::And<
 283                 diesel::expression::operators::Eq<post::columns::id, post_saved::columns::post_id>,
 284                 diesel::expression::operators::Eq<
 285                   post_saved::columns::user_id,
 286                   diesel::expression::bound::Bound<diesel::sql_types::Integer, i32>,
 287                 >,
 288               >,
 289             >,
 290             post_read::table,
 291             LeftOuter,
 292           >,
 293           diesel::expression::operators::And<
 294             diesel::expression::operators::Eq<post::columns::id, post_read::columns::post_id>,
 295             diesel::expression::operators::Eq<
 296               post_read::columns::user_id,
 297               diesel::expression::bound::Bound<diesel::sql_types::Integer, i32>,
 298             >,
 299           >,
 300         >,
 301         post_like::table,
 302         LeftOuter,
 303       >,
 304       diesel::expression::operators::And<
 305         diesel::expression::operators::Eq<post::columns::id, post_like::columns::post_id>,
 306         diesel::expression::operators::Eq<
 307           post_like::columns::user_id,
 308           diesel::expression::bound::Bound<diesel::sql_types::Integer, i32>,
 309         >,
 310       >,
 311     >,
 312     Pg,
 313   >;
 314 }
 315
 316 pub struct PostQueryBuilder<'a> {
 317   conn: &'a PgConnection,
 318   query: join_types::BoxedPostJoin<'a>,
 319   listing_type: &'a ListingType,
 320   sort: &'a SortType,
 321   for_creator_id: Option<i32>,
 322   for_community_id: Option<i32>,
 323   for_community_name: Option<String>,
 324   search_term: Option<String>,
 325   url_search: Option<String>,
 326   show_nsfw: bool,
 327   saved_only: bool,
 328   unread_only: bool,
 329   page: Option<i64>,
 330   limit: Option<i64>,
 331 }
 332
 333 impl<'a> PostQueryBuilder<'a> {
 334   pub fn create(conn: &'a PgConnection, my_user_id: Option<i32>) -> Self {
 335     // The left join below will return None in this case
 336     let user_id_join = my_user_id.unwrap_or(-1);
 337
 338     let query = post::table
 339       .inner_join(user_::table)
 340       .inner_join(community::table)
 341       .inner_join(post_aggregates::table)
 342       .left_join(
 343         community_follower::table.on(
 344           post::community_id
 345             .eq(community_follower::community_id)
 346             .and(community_follower::user_id.eq(user_id_join)),
 347         ),
 348       )
 349       .left_join(
 350         community_user_ban::table.on(
 351           post::community_id
 352             .eq(community_user_ban::community_id)
 353             .and(community_user_ban::user_id.eq(user_id_join)),
 354         ),
 355       )
 356       .left_join(
 357         post_saved::table.on(
 358           post::id
 359             .eq(post_saved::post_id)
 360             .and(post_saved::user_id.eq(user_id_join)),
 361         ),
 362       )
 363       .left_join(
 364         post_read::table.on(
 365           post::id
 366             .eq(post_read::post_id)
 367             .and(post_read::user_id.eq(user_id_join)),
 368         ),
 369       )
 370       .left_join(
 371         post_like::table.on(
 372           post::id
 373             .eq(post_like::post_id)
 374             .and(post_like::user_id.eq(user_id_join)),
 375         ),
 376       )
 377       .select((
 378         post::all_columns,
 379         User_::safe_columns_tuple(),
 380         Community::safe_columns_tuple(),
 381         post_aggregates::all_columns,
 382         community_follower::all_columns.nullable(),
 383         community_user_ban::all_columns.nullable(),
 384         post_saved::all_columns.nullable(),
 385         post_read::all_columns.nullable(),
 386         post_like::score.nullable(),
 387       ))
 388       .into_boxed();
 389
 390     PostQueryBuilder {
 391       conn,
 392       query,
 393       listing_type: &ListingType::All,
 394       sort: &SortType::Hot,
 395       for_creator_id: None,
 396       for_community_id: None,
 397       for_community_name: None,
 398       search_term: None,
 399       url_search: None,
 400       show_nsfw: true,
 401       saved_only: false,
 402       unread_only: false,
 403       page: None,
 404       limit: None,
 405     }
 406   }
 407
 408   pub fn listing_type(mut self, listing_type: &'a ListingType) -> Self {
 409     self.listing_type = listing_type;
 410     self
 411   }
 412
 413   pub fn sort(mut self, sort: &'a SortType) -> Self {
 414     self.sort = sort;
 415     self
 416   }
 417
 418   pub fn for_community_id<T: MaybeOptional<i32>>(mut self, for_community_id: T) -> Self {
 419     self.for_community_id = for_community_id.get_optional();
 420     self
 421   }
 422
 423   pub fn for_community_name<T: MaybeOptional<String>>(mut self, for_community_name: T) -> Self {
 424     self.for_community_name = for_community_name.get_optional();
 425     self
 426   }
 427
 428   pub fn for_creator_id<T: MaybeOptional<i32>>(mut self, for_creator_id: T) -> Self {
 429     self.for_creator_id = for_creator_id.get_optional();
 430     self
 431   }
 432
 433   pub fn search_term<T: MaybeOptional<String>>(mut self, search_term: T) -> Self {
 434     self.search_term = search_term.get_optional();
 435     self
 436   }
 437
 438   pub fn url_search<T: MaybeOptional<String>>(mut self, url_search: T) -> Self {
 439     self.url_search = url_search.get_optional();
 440     self
 441   }
 442
 443   pub fn show_nsfw(mut self, show_nsfw: bool) -> Self {
 444     self.show_nsfw = show_nsfw;
 445     self
 446   }
 447
 448   pub fn saved_only(mut self, saved_only: bool) -> Self {
 449     self.saved_only = saved_only;
 450     self
 451   }
 452
 453   pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
 454     self.page = page.get_optional();
 455     self
 456   }
 457
 458   pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
 459     self.limit = limit.get_optional();
 460     self
 461   }
 462
 463   pub fn list(self) -> Result<Vec<PostView>, Error> {
 464     use diesel::dsl::*;
 465
 466     let mut query = self.query;
 467
 468     query = match self.listing_type {
 469       ListingType::Subscribed => query.filter(community_follower::user_id.is_not_null()), // TODO could be this: and(community_follower::user_id.eq(user_id_join)),
 470       ListingType::Local => query.filter(community::local.eq(true)),
 471       _ => query,
 472     };
 473
 474     if let Some(for_community_id) = self.for_community_id {
 475       query = query
 476         .filter(post::community_id.eq(for_community_id))
 477         .then_order_by(post::stickied.desc());
 478     }
 479
 480     if let Some(for_community_name) = self.for_community_name {
 481       query = query
 482         .filter(community::name.eq(for_community_name))
 483         .filter(community::local.eq(true))
 484         .then_order_by(post::stickied.desc());
 485     }
 486
 487     if let Some(url_search) = self.url_search {
 488       query = query.filter(post::url.eq(url_search));
 489     }
 490
 491     if let Some(search_term) = self.search_term {
 492       let searcher = fuzzy_search(&search_term);
 493       query = query.filter(
 494         post::name
 495           .ilike(searcher.to_owned())
 496           .or(post::body.ilike(searcher)),
 497       );
 498     }
 499
 500     query = match self.sort {
 501       SortType::Active => query
 502         .then_order_by(
 503           hot_rank(post_aggregates::score, post_aggregates::newest_comment_time).desc(),
 504         )
 505         .then_order_by(post::published.desc()),
 506       SortType::Hot => query
 507         .then_order_by(hot_rank(post_aggregates::score, post::published).desc())
 508         .then_order_by(post::published.desc()),
 509       SortType::New => query.then_order_by(post::published.desc()),
 510       SortType::TopAll => query.then_order_by(post_aggregates::score.desc()),
 511       SortType::TopYear => query
 512         .filter(post::published.gt(now - 1.years()))
 513         .then_order_by(post_aggregates::score.desc()),
 514       SortType::TopMonth => query
 515         .filter(post::published.gt(now - 1.months()))
 516         .then_order_by(post_aggregates::score.desc()),
 517       SortType::TopWeek => query
 518         .filter(post::published.gt(now - 1.weeks()))
 519         .then_order_by(post_aggregates::score.desc()),
 520       SortType::TopDay => query
 521         .filter(post::published.gt(now - 1.days()))
 522         .then_order_by(post_aggregates::score.desc()),
 523     };
 524
 525     // If its for a specific user, show the removed / deleted
 526     if let Some(for_creator_id) = self.for_creator_id {
 527       query = query.filter(post::creator_id.eq(for_creator_id));
 528     }
 529
 530     if !self.show_nsfw {
 531       query = query
 532         .filter(post::nsfw.eq(false))
 533         .filter(community::nsfw.eq(false));
 534     };
 535
 536     // TODO  These two might be wrong
 537     if self.saved_only {
 538       query = query.filter(post_saved::id.is_not_null());
 539     };
 540
 541     if self.unread_only {
 542       query = query.filter(post_read::id.is_not_null());
 543     };
 544
 545     let (limit, offset) = limit_and_offset(self.page, self.limit);
 546
 547     let res = query
 548       .limit(limit)
 549       .offset(offset)
 550       .filter(post::removed.eq(false))
 551       .filter(post::deleted.eq(false))
 552       .filter(community::removed.eq(false))
 553       .filter(community::deleted.eq(false))
 554       .load::<OutputTuple>(self.conn)?;
 555
 556     Ok(to_vec(res))
 557   }
 558 }
 559
 560 // TODO turn this into a trait with an associated type
 561 fn to_vec(posts: Vec<OutputTuple>) -> Vec<PostView> {
 562   posts
 563     .iter()
 564     .map(|a| PostView {
 565       post: a.0.to_owned(),
 566       creator: a.1.to_owned(),
 567       community: a.2.to_owned(),
 568       counts: a.3.to_owned(),
 569       subscribed: a.4.is_some(),
 570       banned_from_community: a.5.is_some(),
 571       saved: a.6.is_some(),
 572       read: a.7.is_some(),
 573       my_vote: a.8,
 574     })
 575     .collect::<Vec<PostView>>()
 576 }