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::*, 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 new_person = PersonForm {
 305       name: "timmy_crv".into(),
 306       public_key: Some("pubkey".to_string()),
 307       ..PersonForm::default()
 308     };
 309
 310     let inserted_timmy = Person::create(conn, &new_person).unwrap();
 311
 312     let new_person_2 = PersonForm {
 313       name: "sara_crv".into(),
 314       public_key: Some("pubkey".to_string()),
 315       ..PersonForm::default()
 316     };
 317
 318     let inserted_sara = Person::create(conn, &new_person_2).unwrap();
 319
 320     // Add a third person, since new ppl can only report something once.
 321     let new_person_3 = PersonForm {
 322       name: "jessica_crv".into(),
 323       public_key: Some("pubkey".to_string()),
 324       ..PersonForm::default()
 325     };
 326
 327     let inserted_jessica = Person::create(conn, &new_person_3).unwrap();
 328
 329     let new_community = CommunityForm {
 330       name: "test community crv".to_string(),
 331       title: "nada".to_owned(),
 332       public_key: Some("pubkey".to_string()),
 333       ..CommunityForm::default()
 334     };
 335
 336     let inserted_community = Community::create(conn, &new_community).unwrap();
 337
 338     // Make timmy a mod
 339     let timmy_moderator_form = CommunityModeratorForm {
 340       community_id: inserted_community.id,
 341       person_id: inserted_timmy.id,
 342     };
 343
 344     let _inserted_moderator = CommunityModerator::join(conn, &timmy_moderator_form).unwrap();
 345
 346     let new_post = PostForm {
 347       name: "A test post crv".into(),
 348       creator_id: inserted_timmy.id,
 349       community_id: inserted_community.id,
 350       ..PostForm::default()
 351     };
 352
 353     let inserted_post = Post::create(conn, &new_post).unwrap();
 354
 355     let comment_form = CommentForm {
 356       content: "A test comment 32".into(),
 357       creator_id: inserted_timmy.id,
 358       post_id: inserted_post.id,
 359       ..CommentForm::default()
 360     };
 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       },
 409       creator: PersonSafe {
 410         id: inserted_jessica.id,
 411         name: inserted_jessica.name,
 412         display_name: None,
 413         published: inserted_jessica.published,
 414         avatar: None,
 415         actor_id: inserted_jessica.actor_id.to_owned(),
 416         local: true,
 417         banned: false,
 418         deleted: false,
 419         admin: false,
 420         bot_account: false,
 421         bio: None,
 422         banner: None,
 423         updated: None,
 424         inbox_url: inserted_jessica.inbox_url.to_owned(),
 425         shared_inbox_url: None,
 426         matrix_user_id: None,
 427         ban_expires: None,
 428       },
 429       comment_creator: PersonSafe {
 430         id: inserted_timmy.id,
 431         name: inserted_timmy.name.to_owned(),
 432         display_name: None,
 433         published: inserted_timmy.published,
 434         avatar: None,
 435         actor_id: inserted_timmy.actor_id.to_owned(),
 436         local: true,
 437         banned: false,
 438         deleted: false,
 439         admin: false,
 440         bot_account: false,
 441         bio: None,
 442         banner: None,
 443         updated: None,
 444         inbox_url: inserted_timmy.inbox_url.to_owned(),
 445         shared_inbox_url: None,
 446         matrix_user_id: None,
 447         ban_expires: None,
 448       },
 449       creator_banned_from_community: false,
 450       counts: CommentAggregates {
 451         id: agg.id,
 452         comment_id: inserted_comment.id,
 453         score: 0,
 454         upvotes: 0,
 455         downvotes: 0,
 456         published: agg.published,
 457         child_count: 0,
 458       },
 459       my_vote: None,
 460       resolver: None,
 461     };
 462
 463     assert_eq!(read_jessica_report_view, expected_jessica_report_view);
 464
 465     let mut expected_sara_report_view = expected_jessica_report_view.clone();
 466     expected_sara_report_view.comment_report = inserted_sara_report;
 467     expected_sara_report_view.creator = PersonSafe {
 468       id: inserted_sara.id,
 469       name: inserted_sara.name,
 470       display_name: None,
 471       published: inserted_sara.published,
 472       avatar: None,
 473       actor_id: inserted_sara.actor_id.to_owned(),
 474       local: true,
 475       banned: false,
 476       deleted: false,
 477       admin: false,
 478       bot_account: false,
 479       bio: None,
 480       banner: None,
 481       updated: None,
 482       inbox_url: inserted_sara.inbox_url.to_owned(),
 483       shared_inbox_url: None,
 484       matrix_user_id: None,
 485       ban_expires: None,
 486     };
 487
 488     // Do a batch read of timmys reports
 489     let reports = CommentReportQuery::builder()
 490       .conn(conn)
 491       .my_person_id(inserted_timmy.id)
 492       .admin(false)
 493       .build()
 494       .list()
 495       .unwrap();
 496
 497     assert_eq!(
 498       reports,
 499       [
 500         expected_jessica_report_view.to_owned(),
 501         expected_sara_report_view.to_owned()
 502       ]
 503     );
 504
 505     // Make sure the counts are correct
 506     let report_count =
 507       CommentReportView::get_report_count(conn, inserted_timmy.id, false, None).unwrap();
 508     assert_eq!(2, report_count);
 509
 510     // Try to resolve the report
 511     CommentReport::resolve(conn, inserted_jessica_report.id, inserted_timmy.id).unwrap();
 512     let read_jessica_report_view_after_resolve =
 513       CommentReportView::read(conn, inserted_jessica_report.id, inserted_timmy.id).unwrap();
 514
 515     let mut expected_jessica_report_view_after_resolve = expected_jessica_report_view;
 516     expected_jessica_report_view_after_resolve
 517       .comment_report
 518       .resolved = true;
 519     expected_jessica_report_view_after_resolve
 520       .comment_report
 521       .resolver_id = Some(inserted_timmy.id);
 522     expected_jessica_report_view_after_resolve
 523       .comment_report
 524       .updated = read_jessica_report_view_after_resolve
 525       .comment_report
 526       .updated;
 527     expected_jessica_report_view_after_resolve.resolver = Some(PersonSafe {
 528       id: inserted_timmy.id,
 529       name: inserted_timmy.name.to_owned(),
 530       display_name: None,
 531       published: inserted_timmy.published,
 532       avatar: None,
 533       actor_id: inserted_timmy.actor_id.to_owned(),
 534       local: true,
 535       banned: false,
 536       deleted: false,
 537       admin: false,
 538       bot_account: false,
 539       bio: None,
 540       banner: None,
 541       updated: None,
 542       inbox_url: inserted_timmy.inbox_url.to_owned(),
 543       shared_inbox_url: None,
 544       matrix_user_id: None,
 545       ban_expires: None,
 546     });
 547
 548     assert_eq!(
 549       read_jessica_report_view_after_resolve,
 550       expected_jessica_report_view_after_resolve
 551     );
 552
 553     // Do a batch read of timmys reports
 554     // It should only show saras, which is unresolved
 555     let reports_after_resolve = CommentReportQuery::builder()
 556       .conn(conn)
 557       .my_person_id(inserted_timmy.id)
 558       .admin(false)
 559       .build()
 560       .list()
 561       .unwrap();
 562     assert_eq!(reports_after_resolve[0], expected_sara_report_view);
 563     assert_eq!(reports_after_resolve.len(), 1);
 564
 565     // Make sure the counts are correct
 566     let report_count_after_resolved =
 567       CommentReportView::get_report_count(conn, inserted_timmy.id, false, None).unwrap();
 568     assert_eq!(1, report_count_after_resolved);
 569
 570     Person::delete(conn, inserted_timmy.id).unwrap();
 571     Person::delete(conn, inserted_sara.id).unwrap();
 572     Person::delete(conn, inserted_jessica.id).unwrap();
 573     Community::delete(conn, inserted_community.id).unwrap();
 574   }
 575 }