crates/db_views/src/post_report_view.rs

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