crates/db_views/src/comment_report_view.rs

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