crates/db_views/src/comment_report_view.rs

   1 use diesel::{result::Error, *};
   2 use lemmy_db_queries::{
   3   aggregates::comment_aggregates::CommentAggregates,
   4   limit_and_offset,
   5   MaybeOptional,
   6   ToSafe,
   7   ViewToVec,
   8 };
   9 use lemmy_db_schema::{
  10   schema::{
  11     comment,
  12     comment_aggregates,
  13     comment_like,
  14     comment_report,
  15     community,
  16     community_moderator,
  17     community_person_ban,
  18     person,
  19     person_alias_1,
  20     person_alias_2,
  21     post,
  22   },
  23   source::{
  24     comment::Comment,
  25     comment_report::CommentReport,
  26     community::{Community, CommunityPersonBan, CommunitySafe},
  27     person::{Person, PersonAlias1, PersonAlias2, PersonSafe, PersonSafeAlias1, PersonSafeAlias2},
  28     post::Post,
  29   },
  30   CommentReportId,
  31   CommunityId,
  32   PersonId,
  33 };
  34 use serde::Serialize;
  35
  36 #[derive(Debug, PartialEq, Serialize, Clone)]
  37 pub struct CommentReportView {
  38   pub comment_report: CommentReport,
  39   pub comment: Comment,
  40   pub post: Post,
  41   pub community: CommunitySafe,
  42   pub creator: PersonSafe,
  43   pub comment_creator: PersonSafeAlias1,
  44   pub counts: CommentAggregates,
  45   pub creator_banned_from_community: bool, // Left Join to CommunityPersonBan
  46   pub my_vote: Option<i16>,                // Left join to CommentLike
  47   pub resolver: Option<PersonSafeAlias2>,
  48 }
  49
  50 type CommentReportViewTuple = (
  51   CommentReport,
  52   Comment,
  53   Post,
  54   CommunitySafe,
  55   PersonSafe,
  56   PersonSafeAlias1,
  57   CommentAggregates,
  58   Option<CommunityPersonBan>,
  59   Option<i16>,
  60   Option<PersonSafeAlias2>,
  61 );
  62
  63 impl CommentReportView {
  64   /// returns the CommentReportView for the provided report_id
  65   ///
  66   /// * `report_id` - the report id to obtain
  67   pub fn read(
  68     conn: &PgConnection,
  69     report_id: CommentReportId,
  70     my_person_id: PersonId,
  71   ) -> Result<Self, Error> {
  72     let (
  73       comment_report,
  74       comment,
  75       post,
  76       community,
  77       creator,
  78       comment_creator,
  79       counts,
  80       creator_banned_from_community,
  81       comment_like,
  82       resolver,
  83     ) = comment_report::table
  84       .find(report_id)
  85       .inner_join(comment::table)
  86       .inner_join(post::table.on(comment::post_id.eq(post::id)))
  87       .inner_join(community::table.on(post::community_id.eq(community::id)))
  88       .inner_join(person::table.on(comment_report::creator_id.eq(person::id)))
  89       .inner_join(person_alias_1::table.on(post::creator_id.eq(person_alias_1::id)))
  90       .inner_join(
  91         comment_aggregates::table.on(comment_report::comment_id.eq(comment_aggregates::comment_id)),
  92       )
  93       .left_join(
  94         community_person_ban::table.on(
  95           community::id
  96             .eq(community_person_ban::community_id)
  97             .and(community_person_ban::person_id.eq(comment::creator_id)),
  98         ),
  99       )
 100       .left_join(
 101         comment_like::table.on(
 102           comment::id
 103             .eq(comment_like::comment_id)
 104             .and(comment_like::person_id.eq(my_person_id)),
 105         ),
 106       )
 107       .left_join(
 108         person_alias_2::table.on(comment_report::resolver_id.eq(person_alias_2::id.nullable())),
 109       )
 110       .select((
 111         comment_report::all_columns,
 112         comment::all_columns,
 113         post::all_columns,
 114         Community::safe_columns_tuple(),
 115         Person::safe_columns_tuple(),
 116         PersonAlias1::safe_columns_tuple(),
 117         comment_aggregates::all_columns,
 118         community_person_ban::all_columns.nullable(),
 119         comment_like::score.nullable(),
 120         PersonAlias2::safe_columns_tuple().nullable(),
 121       ))
 122       .first::<CommentReportViewTuple>(conn)?;
 123
 124     let my_vote = if comment_like.is_none() {
 125       None
 126     } else {
 127       comment_like
 128     };
 129
 130     Ok(Self {
 131       comment_report,
 132       comment,
 133       post,
 134       community,
 135       creator,
 136       comment_creator,
 137       counts,
 138       creator_banned_from_community: creator_banned_from_community.is_some(),
 139       my_vote,
 140       resolver,
 141     })
 142   }
 143
 144   /// Returns the current unresolved post report count for the communities you mod
 145   pub fn get_report_count(
 146     conn: &PgConnection,
 147     my_person_id: PersonId,
 148     admin: bool,
 149     community_id: Option<CommunityId>,
 150   ) -> Result<i64, Error> {
 151     use diesel::dsl::*;
 152
 153     let mut query = comment_report::table
 154       .inner_join(comment::table)
 155       .inner_join(post::table.on(comment::post_id.eq(post::id)))
 156       .inner_join(
 157         community_moderator::table.on(community_moderator::community_id.eq(post::community_id)),
 158       )
 159       .filter(comment_report::resolved.eq(false))
 160       .into_boxed();
 161
 162     // If its not an admin, get only the ones you mod
 163     if !admin {
 164       query = query.filter(community_moderator::person_id.eq(my_person_id));
 165     }
 166
 167     if let Some(community_id) = community_id {
 168       query = query.filter(post::community_id.eq(community_id))
 169     }
 170
 171     query.select(count(comment_report::id)).first::<i64>(conn)
 172   }
 173 }
 174
 175 pub struct CommentReportQueryBuilder<'a> {
 176   conn: &'a PgConnection,
 177   my_person_id: PersonId,
 178   admin: bool,
 179   community_id: Option<CommunityId>,
 180   page: Option<i64>,
 181   limit: Option<i64>,
 182   unresolved_only: Option<bool>,
 183 }
 184
 185 impl<'a> CommentReportQueryBuilder<'a> {
 186   pub fn create(conn: &'a PgConnection, my_person_id: PersonId, admin: bool) -> Self {
 187     CommentReportQueryBuilder {
 188       conn,
 189       my_person_id,
 190       admin,
 191       community_id: None,
 192       page: None,
 193       limit: None,
 194       unresolved_only: Some(true),
 195     }
 196   }
 197
 198   pub fn community_id<T: MaybeOptional<CommunityId>>(mut self, community_id: T) -> Self {
 199     self.community_id = community_id.get_optional();
 200     self
 201   }
 202
 203   pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
 204     self.page = page.get_optional();
 205     self
 206   }
 207
 208   pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
 209     self.limit = limit.get_optional();
 210     self
 211   }
 212
 213   pub fn unresolved_only<T: MaybeOptional<bool>>(mut self, unresolved_only: T) -> Self {
 214     self.unresolved_only = unresolved_only.get_optional();
 215     self
 216   }
 217
 218   pub fn list(self) -> Result<Vec<CommentReportView>, Error> {
 219     let mut query = comment_report::table
 220       .inner_join(comment::table)
 221       .inner_join(post::table.on(comment::post_id.eq(post::id)))
 222       .inner_join(community::table.on(post::community_id.eq(community::id)))
 223       .inner_join(person::table.on(comment_report::creator_id.eq(person::id)))
 224       .inner_join(person_alias_1::table.on(post::creator_id.eq(person_alias_1::id)))
 225       // Test this join
 226       .inner_join(
 227         community_moderator::table.on(community_moderator::community_id.eq(post::community_id)),
 228       )
 229       .inner_join(
 230         comment_aggregates::table.on(comment_report::comment_id.eq(comment_aggregates::comment_id)),
 231       )
 232       .left_join(
 233         community_person_ban::table.on(
 234           community::id
 235             .eq(community_person_ban::community_id)
 236             .and(community_person_ban::person_id.eq(comment::creator_id)),
 237         ),
 238       )
 239       .left_join(
 240         comment_like::table.on(
 241           comment::id
 242             .eq(comment_like::comment_id)
 243             .and(comment_like::person_id.eq(self.my_person_id)),
 244         ),
 245       )
 246       .left_join(
 247         person_alias_2::table.on(comment_report::resolver_id.eq(person_alias_2::id.nullable())),
 248       )
 249       .select((
 250         comment_report::all_columns,
 251         comment::all_columns,
 252         post::all_columns,
 253         Community::safe_columns_tuple(),
 254         Person::safe_columns_tuple(),
 255         PersonAlias1::safe_columns_tuple(),
 256         comment_aggregates::all_columns,
 257         community_person_ban::all_columns.nullable(),
 258         comment_like::score.nullable(),
 259         PersonAlias2::safe_columns_tuple().nullable(),
 260       ))
 261       .into_boxed();
 262
 263     // If its not an admin, get only the ones you mod
 264     if !self.admin {
 265       query = query.filter(community_moderator::person_id.eq(self.my_person_id));
 266     }
 267
 268     if let Some(community_id) = self.community_id {
 269       query = query.filter(post::community_id.eq(community_id));
 270     }
 271
 272     if self.unresolved_only.unwrap_or(false) {
 273       query = query.filter(comment_report::resolved.eq(false));
 274     }
 275
 276     let (limit, offset) = limit_and_offset(self.page, self.limit);
 277
 278     let res = query
 279       .order_by(comment_report::published.asc())
 280       .limit(limit)
 281       .offset(offset)
 282       .load::<CommentReportViewTuple>(self.conn)?;
 283
 284     Ok(CommentReportView::from_tuple_to_vec(res))
 285   }
 286 }
 287
 288 impl ViewToVec for CommentReportView {
 289   type DbTuple = CommentReportViewTuple;
 290   fn from_tuple_to_vec(items: Vec<Self::DbTuple>) -> Vec<Self> {
 291     items
 292       .iter()
 293       .map(|a| Self {
 294         comment_report: a.0.to_owned(),
 295         comment: a.1.to_owned(),
 296         post: a.2.to_owned(),
 297         community: a.3.to_owned(),
 298         creator: a.4.to_owned(),
 299         comment_creator: a.5.to_owned(),
 300         counts: a.6.to_owned(),
 301         creator_banned_from_community: a.7.is_some(),
 302         my_vote: a.8,
 303         resolver: a.9.to_owned(),
 304       })
 305       .collect::<Vec<Self>>()
 306   }
 307 }
 308
 309 #[cfg(test)]
 310 mod tests {
 311   use crate::comment_report_view::{CommentReportQueryBuilder, CommentReportView};
 312   use lemmy_db_queries::{
 313     aggregates::comment_aggregates::CommentAggregates,
 314     establish_unpooled_connection,
 315     Crud,
 316     Joinable,
 317     Reportable,
 318   };
 319   use lemmy_db_schema::source::{comment::*, comment_report::*, community::*, person::*, post::*};
 320   use serial_test::serial;
 321
 322   #[test]
 323   #[serial]
 324   fn test_crud() {
 325     let conn = establish_unpooled_connection();
 326
 327     let new_person = PersonForm {
 328       name: "timmy_crv".into(),
 329       ..PersonForm::default()
 330     };
 331
 332     let inserted_timmy = Person::create(&conn, &new_person).unwrap();
 333
 334     let new_person_2 = PersonForm {
 335       name: "sara_crv".into(),
 336       ..PersonForm::default()
 337     };
 338
 339     let inserted_sara = Person::create(&conn, &new_person_2).unwrap();
 340
 341     // Add a third person, since new ppl can only report something once.
 342     let new_person_3 = PersonForm {
 343       name: "jessica_crv".into(),
 344       ..PersonForm::default()
 345     };
 346
 347     let inserted_jessica = Person::create(&conn, &new_person_3).unwrap();
 348
 349     let new_community = CommunityForm {
 350       name: "test community crv".to_string(),
 351       title: "nada".to_owned(),
 352       ..CommunityForm::default()
 353     };
 354
 355     let inserted_community = Community::create(&conn, &new_community).unwrap();
 356
 357     // Make timmy a mod
 358     let timmy_moderator_form = CommunityModeratorForm {
 359       community_id: inserted_community.id,
 360       person_id: inserted_timmy.id,
 361     };
 362
 363     let _inserted_moderator = CommunityModerator::join(&conn, &timmy_moderator_form).unwrap();
 364
 365     let new_post = PostForm {
 366       name: "A test post crv".into(),
 367       creator_id: inserted_timmy.id,
 368       community_id: inserted_community.id,
 369       ..PostForm::default()
 370     };
 371
 372     let inserted_post = Post::create(&conn, &new_post).unwrap();
 373
 374     let comment_form = CommentForm {
 375       content: "A test comment 32".into(),
 376       creator_id: inserted_timmy.id,
 377       post_id: inserted_post.id,
 378       ..CommentForm::default()
 379     };
 380
 381     let inserted_comment = Comment::create(&conn, &comment_form).unwrap();
 382
 383     // sara reports
 384     let sara_report_form = CommentReportForm {
 385       creator_id: inserted_sara.id,
 386       comment_id: inserted_comment.id,
 387       original_comment_text: "this was it at time of creation".into(),
 388       reason: "from sara".into(),
 389     };
 390
 391     let inserted_sara_report = CommentReport::report(&conn, &sara_report_form).unwrap();
 392
 393     // jessica reports
 394     let jessica_report_form = CommentReportForm {
 395       creator_id: inserted_jessica.id,
 396       comment_id: inserted_comment.id,
 397       original_comment_text: "this was it at time of creation".into(),
 398       reason: "from jessica".into(),
 399     };
 400
 401     let inserted_jessica_report = CommentReport::report(&conn, &jessica_report_form).unwrap();
 402
 403     let agg = CommentAggregates::read(&conn, inserted_comment.id).unwrap();
 404
 405     let read_jessica_report_view =
 406       CommentReportView::read(&conn, inserted_jessica_report.id, inserted_timmy.id).unwrap();
 407     let expected_jessica_report_view = CommentReportView {
 408       comment_report: inserted_jessica_report.to_owned(),
 409       comment: inserted_comment.to_owned(),
 410       post: inserted_post,
 411       community: CommunitySafe {
 412         id: inserted_community.id,
 413         name: inserted_community.name,
 414         icon: None,
 415         removed: false,
 416         deleted: false,
 417         nsfw: false,
 418         actor_id: inserted_community.actor_id.to_owned(),
 419         local: true,
 420         title: inserted_community.title,
 421         description: None,
 422         updated: None,
 423         banner: None,
 424         published: inserted_community.published,
 425       },
 426       creator: PersonSafe {
 427         id: inserted_jessica.id,
 428         name: inserted_jessica.name,
 429         display_name: None,
 430         published: inserted_jessica.published,
 431         avatar: None,
 432         actor_id: inserted_jessica.actor_id.to_owned(),
 433         local: true,
 434         banned: false,
 435         deleted: false,
 436         admin: false,
 437         bot_account: false,
 438         bio: None,
 439         banner: None,
 440         updated: None,
 441         inbox_url: inserted_jessica.inbox_url.to_owned(),
 442         shared_inbox_url: None,
 443         matrix_user_id: None,
 444       },
 445       comment_creator: PersonSafeAlias1 {
 446         id: inserted_timmy.id,
 447         name: inserted_timmy.name.to_owned(),
 448         display_name: None,
 449         published: inserted_timmy.published,
 450         avatar: None,
 451         actor_id: inserted_timmy.actor_id.to_owned(),
 452         local: true,
 453         banned: false,
 454         deleted: false,
 455         admin: false,
 456         bot_account: false,
 457         bio: None,
 458         banner: None,
 459         updated: None,
 460         inbox_url: inserted_timmy.inbox_url.to_owned(),
 461         shared_inbox_url: None,
 462         matrix_user_id: None,
 463       },
 464       creator_banned_from_community: false,
 465       counts: CommentAggregates {
 466         id: agg.id,
 467         comment_id: inserted_comment.id,
 468         score: 0,
 469         upvotes: 0,
 470         downvotes: 0,
 471         published: agg.published,
 472       },
 473       my_vote: None,
 474       resolver: None,
 475     };
 476
 477     assert_eq!(read_jessica_report_view, expected_jessica_report_view);
 478
 479     let mut expected_sara_report_view = expected_jessica_report_view.clone();
 480     expected_sara_report_view.comment_report = inserted_sara_report;
 481     expected_sara_report_view.creator = PersonSafe {
 482       id: inserted_sara.id,
 483       name: inserted_sara.name,
 484       display_name: None,
 485       published: inserted_sara.published,
 486       avatar: None,
 487       actor_id: inserted_sara.actor_id.to_owned(),
 488       local: true,
 489       banned: false,
 490       deleted: false,
 491       admin: false,
 492       bot_account: false,
 493       bio: None,
 494       banner: None,
 495       updated: None,
 496       inbox_url: inserted_sara.inbox_url.to_owned(),
 497       shared_inbox_url: None,
 498       matrix_user_id: None,
 499     };
 500
 501     // Do a batch read of timmys reports
 502     let reports = CommentReportQueryBuilder::create(&conn, inserted_timmy.id, false)
 503       .list()
 504       .unwrap();
 505
 506     assert_eq!(
 507       reports,
 508       [
 509         expected_sara_report_view.to_owned(),
 510         expected_jessica_report_view.to_owned()
 511       ]
 512     );
 513
 514     // Make sure the counts are correct
 515     let report_count =
 516       CommentReportView::get_report_count(&conn, inserted_timmy.id, false, None).unwrap();
 517     assert_eq!(2, report_count);
 518
 519     // Try to resolve the report
 520     CommentReport::resolve(&conn, inserted_jessica_report.id, inserted_timmy.id).unwrap();
 521     let read_jessica_report_view_after_resolve =
 522       CommentReportView::read(&conn, inserted_jessica_report.id, inserted_timmy.id).unwrap();
 523
 524     let mut expected_jessica_report_view_after_resolve = expected_jessica_report_view;
 525     expected_jessica_report_view_after_resolve
 526       .comment_report
 527       .resolved = true;
 528     expected_jessica_report_view_after_resolve
 529       .comment_report
 530       .resolver_id = Some(inserted_timmy.id);
 531     expected_jessica_report_view_after_resolve
 532       .comment_report
 533       .updated = read_jessica_report_view_after_resolve
 534       .comment_report
 535       .updated;
 536     expected_jessica_report_view_after_resolve.resolver = Some(PersonSafeAlias2 {
 537       id: inserted_timmy.id,
 538       name: inserted_timmy.name.to_owned(),
 539       display_name: None,
 540       published: inserted_timmy.published,
 541       avatar: None,
 542       actor_id: inserted_timmy.actor_id.to_owned(),
 543       local: true,
 544       banned: false,
 545       deleted: false,
 546       admin: false,
 547       bot_account: false,
 548       bio: None,
 549       banner: None,
 550       updated: None,
 551       inbox_url: inserted_timmy.inbox_url.to_owned(),
 552       shared_inbox_url: None,
 553       matrix_user_id: None,
 554     });
 555
 556     assert_eq!(
 557       read_jessica_report_view_after_resolve,
 558       expected_jessica_report_view_after_resolve
 559     );
 560
 561     // Do a batch read of timmys reports
 562     // It should only show saras, which is unresolved
 563     let reports_after_resolve = CommentReportQueryBuilder::create(&conn, inserted_timmy.id, false)
 564       .list()
 565       .unwrap();
 566     assert_eq!(reports_after_resolve[0], expected_sara_report_view);
 567
 568     // Make sure the counts are correct
 569     let report_count_after_resolved =
 570       CommentReportView::get_report_count(&conn, inserted_timmy.id, false, None).unwrap();
 571     assert_eq!(1, report_count_after_resolved);
 572
 573     Person::delete(&conn, inserted_timmy.id).unwrap();
 574     Person::delete(&conn, inserted_sara.id).unwrap();
 575     Person::delete(&conn, inserted_jessica.id).unwrap();
 576     Community::delete(&conn, inserted_community.id).unwrap();
 577   }
 578 }