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       person::*,
 276       post::*,
 277       post_report::{PostReport, PostReportForm},
 278     },
 279     traits::{Crud, Joinable, Reportable},
 280     utils::establish_unpooled_connection,
 281   };
 282   use serial_test::serial;
 283
 284   #[test]
 285   #[serial]
 286   fn test_crud() {
 287     let conn = &mut establish_unpooled_connection();
 288
 289     let new_person = PersonForm {
 290       name: "timmy_prv".into(),
 291       public_key: Some("pubkey".to_string()),
 292       ..PersonForm::default()
 293     };
 294
 295     let inserted_timmy = Person::create(conn, &new_person).unwrap();
 296
 297     let new_person_2 = PersonForm {
 298       name: "sara_prv".into(),
 299       public_key: Some("pubkey".to_string()),
 300       ..PersonForm::default()
 301     };
 302
 303     let inserted_sara = Person::create(conn, &new_person_2).unwrap();
 304
 305     // Add a third person, since new ppl can only report something once.
 306     let new_person_3 = PersonForm {
 307       name: "jessica_prv".into(),
 308       public_key: Some("pubkey".to_string()),
 309       ..PersonForm::default()
 310     };
 311
 312     let inserted_jessica = Person::create(conn, &new_person_3).unwrap();
 313
 314     let new_community = CommunityForm {
 315       name: "test community prv".to_string(),
 316       title: "nada".to_owned(),
 317       public_key: Some("pubkey".to_string()),
 318       ..CommunityForm::default()
 319     };
 320
 321     let inserted_community = Community::create(conn, &new_community).unwrap();
 322
 323     // Make timmy a mod
 324     let timmy_moderator_form = CommunityModeratorForm {
 325       community_id: inserted_community.id,
 326       person_id: inserted_timmy.id,
 327     };
 328
 329     let _inserted_moderator = CommunityModerator::join(conn, &timmy_moderator_form).unwrap();
 330
 331     let new_post = PostForm {
 332       name: "A test post crv".into(),
 333       creator_id: inserted_timmy.id,
 334       community_id: inserted_community.id,
 335       ..PostForm::default()
 336     };
 337
 338     let inserted_post = Post::create(conn, &new_post).unwrap();
 339
 340     // sara reports
 341     let sara_report_form = PostReportForm {
 342       creator_id: inserted_sara.id,
 343       post_id: inserted_post.id,
 344       original_post_name: "Orig post".into(),
 345       original_post_url: None,
 346       original_post_body: None,
 347       reason: "from sara".into(),
 348     };
 349
 350     let inserted_sara_report = PostReport::report(conn, &sara_report_form).unwrap();
 351
 352     // jessica reports
 353     let jessica_report_form = PostReportForm {
 354       creator_id: inserted_jessica.id,
 355       post_id: inserted_post.id,
 356       original_post_name: "Orig post".into(),
 357       original_post_url: None,
 358       original_post_body: None,
 359       reason: "from jessica".into(),
 360     };
 361
 362     let inserted_jessica_report = PostReport::report(conn, &jessica_report_form).unwrap();
 363
 364     let agg = PostAggregates::read(conn, inserted_post.id).unwrap();
 365
 366     let read_jessica_report_view =
 367       PostReportView::read(conn, inserted_jessica_report.id, inserted_timmy.id).unwrap();
 368     let expected_jessica_report_view = PostReportView {
 369       post_report: inserted_jessica_report.to_owned(),
 370       post: inserted_post.to_owned(),
 371       community: CommunitySafe {
 372         id: inserted_community.id,
 373         name: inserted_community.name,
 374         icon: None,
 375         removed: false,
 376         deleted: false,
 377         nsfw: false,
 378         actor_id: inserted_community.actor_id.to_owned(),
 379         local: true,
 380         title: inserted_community.title,
 381         description: None,
 382         updated: None,
 383         banner: None,
 384         hidden: false,
 385         posting_restricted_to_mods: false,
 386         published: inserted_community.published,
 387       },
 388       creator: PersonSafe {
 389         id: inserted_jessica.id,
 390         name: inserted_jessica.name,
 391         display_name: None,
 392         published: inserted_jessica.published,
 393         avatar: None,
 394         actor_id: inserted_jessica.actor_id.to_owned(),
 395         local: true,
 396         banned: false,
 397         deleted: false,
 398         admin: false,
 399         bot_account: false,
 400         bio: None,
 401         banner: None,
 402         updated: None,
 403         inbox_url: inserted_jessica.inbox_url.to_owned(),
 404         shared_inbox_url: None,
 405         matrix_user_id: None,
 406         ban_expires: None,
 407       },
 408       post_creator: PersonSafe {
 409         id: inserted_timmy.id,
 410         name: inserted_timmy.name.to_owned(),
 411         display_name: None,
 412         published: inserted_timmy.published,
 413         avatar: None,
 414         actor_id: inserted_timmy.actor_id.to_owned(),
 415         local: true,
 416         banned: false,
 417         deleted: false,
 418         admin: false,
 419         bot_account: false,
 420         bio: None,
 421         banner: None,
 422         updated: None,
 423         inbox_url: inserted_timmy.inbox_url.to_owned(),
 424         shared_inbox_url: None,
 425         matrix_user_id: None,
 426         ban_expires: None,
 427       },
 428       creator_banned_from_community: false,
 429       my_vote: None,
 430       counts: PostAggregates {
 431         id: agg.id,
 432         post_id: inserted_post.id,
 433         comments: 0,
 434         score: 0,
 435         upvotes: 0,
 436         downvotes: 0,
 437         stickied: false,
 438         published: agg.published,
 439         newest_comment_time_necro: inserted_post.published,
 440         newest_comment_time: inserted_post.published,
 441       },
 442       resolver: None,
 443     };
 444
 445     assert_eq!(read_jessica_report_view, expected_jessica_report_view);
 446
 447     let mut expected_sara_report_view = expected_jessica_report_view.clone();
 448     expected_sara_report_view.post_report = inserted_sara_report;
 449     expected_sara_report_view.my_vote = None;
 450     expected_sara_report_view.creator = PersonSafe {
 451       id: inserted_sara.id,
 452       name: inserted_sara.name,
 453       display_name: None,
 454       published: inserted_sara.published,
 455       avatar: None,
 456       actor_id: inserted_sara.actor_id.to_owned(),
 457       local: true,
 458       banned: false,
 459       deleted: false,
 460       admin: false,
 461       bot_account: false,
 462       bio: None,
 463       banner: None,
 464       updated: None,
 465       inbox_url: inserted_sara.inbox_url.to_owned(),
 466       shared_inbox_url: None,
 467       matrix_user_id: None,
 468       ban_expires: None,
 469     };
 470
 471     // Do a batch read of timmys reports
 472     let reports = PostReportQuery::builder()
 473       .conn(conn)
 474       .my_person_id(inserted_timmy.id)
 475       .admin(false)
 476       .build()
 477       .list()
 478       .unwrap();
 479
 480     assert_eq!(
 481       reports,
 482       [
 483         expected_jessica_report_view.to_owned(),
 484         expected_sara_report_view.to_owned()
 485       ]
 486     );
 487
 488     // Make sure the counts are correct
 489     let report_count =
 490       PostReportView::get_report_count(conn, inserted_timmy.id, false, None).unwrap();
 491     assert_eq!(2, report_count);
 492
 493     // Try to resolve the report
 494     PostReport::resolve(conn, inserted_jessica_report.id, inserted_timmy.id).unwrap();
 495     let read_jessica_report_view_after_resolve =
 496       PostReportView::read(conn, inserted_jessica_report.id, inserted_timmy.id).unwrap();
 497
 498     let mut expected_jessica_report_view_after_resolve = expected_jessica_report_view;
 499     expected_jessica_report_view_after_resolve
 500       .post_report
 501       .resolved = true;
 502     expected_jessica_report_view_after_resolve
 503       .post_report
 504       .resolver_id = Some(inserted_timmy.id);
 505     expected_jessica_report_view_after_resolve
 506       .post_report
 507       .updated = read_jessica_report_view_after_resolve.post_report.updated;
 508     expected_jessica_report_view_after_resolve.resolver = Some(PersonSafe {
 509       id: inserted_timmy.id,
 510       name: inserted_timmy.name.to_owned(),
 511       display_name: None,
 512       published: inserted_timmy.published,
 513       avatar: None,
 514       actor_id: inserted_timmy.actor_id.to_owned(),
 515       local: true,
 516       banned: false,
 517       deleted: false,
 518       admin: false,
 519       bot_account: false,
 520       bio: None,
 521       banner: None,
 522       updated: None,
 523       inbox_url: inserted_timmy.inbox_url.to_owned(),
 524       shared_inbox_url: None,
 525       matrix_user_id: None,
 526       ban_expires: None,
 527     });
 528
 529     assert_eq!(
 530       read_jessica_report_view_after_resolve,
 531       expected_jessica_report_view_after_resolve
 532     );
 533
 534     // Do a batch read of timmys reports
 535     // It should only show saras, which is unresolved
 536     let reports_after_resolve = PostReportQuery::builder()
 537       .conn(conn)
 538       .my_person_id(inserted_timmy.id)
 539       .admin(false)
 540       .build()
 541       .list()
 542       .unwrap();
 543     assert_eq!(reports_after_resolve[0], expected_sara_report_view);
 544
 545     // Make sure the counts are correct
 546     let report_count_after_resolved =
 547       PostReportView::get_report_count(conn, inserted_timmy.id, false, None).unwrap();
 548     assert_eq!(1, report_count_after_resolved);
 549
 550     Person::delete(conn, inserted_timmy.id).unwrap();
 551     Person::delete(conn, inserted_sara.id).unwrap();
 552     Person::delete(conn, inserted_jessica.id).unwrap();
 553     Community::delete(conn, inserted_community.id).unwrap();
 554   }
 555 }