crates/db_views/src/comment_report_view.rs

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