crates/db_views/src/comment_report_view.rs

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