lemmy_db/src/views/comment_view.rs

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