Faster /reports for inspectors, reduce db queries.

3 files changed, 25 insertions, 2 deletions

diff --git a/perllib/FixMyStreet/App/Controller/Reports.pm b/perllib/FixMyStreet/App/Controller/Reports.pm
index 2b5777773..0e5a339d7 100644
--- a/perllib/FixMyStreet/App/Controller/Reports.pm
+++ b/perllib/FixMyStreet/App/Controller/Reports.pm
@@ -413,6 +413,9 @@ sub load_and_group_problems : Private {
         order_by => $c->stash->{sort_order},
         rows => $c->cobrand->reports_per_page,
     };
+    if ($c->user_exists && $c->stash->{body} && $c->user->has_permission_to('planned_reports', $c->stash->{body}->id)) {
+        $filter->{prefetch} = 'user_planned_reports';
+    }
 
     if (defined $c->stash->{filter_status}{shortlisted}) {
         $where->{'me.id'} = { '=', \"user_planned_reports.report_id"};
diff --git a/perllib/FixMyStreet/DB/Result/User.pm b/perllib/FixMyStreet/DB/Result/User.pm
index 8385d2eea..e44b2530f 100644
--- a/perllib/FixMyStreet/DB/Result/User.pm
+++ b/perllib/FixMyStreet/DB/Result/User.pm
@@ -412,9 +412,19 @@ sub active_planned_reports {
     $self->planned_reports->search({ removed => undef });
 }
 
+has active_user_planned_reports => (
+    is => 'ro',
+    lazy => 1,
+    default => sub {
+        my $self = shift;
+        [ $self->user_planned_reports->search({ removed => undef })->all ];
+    },
+);
+
 sub is_planned_report {
     my ($self, $problem) = @_;
-    return $self->active_planned_reports->find({ id => $problem->id });
+    my $id = $problem->id;
+    return scalar grep { $_->report_id == $id } @{$self->active_user_planned_reports};
 }
 
 sub update_reputation {
diff --git a/perllib/FixMyStreet/DB/ResultSet/UserPlannedReport.pm b/perllib/FixMyStreet/DB/ResultSet/UserPlannedReport.pm
index 7e16e2dd3..460a4912e 100644
--- a/perllib/FixMyStreet/DB/ResultSet/UserPlannedReport.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/UserPlannedReport.pm
@@ -6,7 +6,17 @@ use warnings;
 
 sub active {
     my $rs = shift;
-    $rs->search({ removed => undef });
+
+    # If we have been prefetched we can't use `active` as that'll blow away the
+    # cache and query the DB due to the `removed IS NULL` clause. So let's do
+    # the filtering here instead, if the query has been prefetched.
+    if ( $rs->get_cache ) {
+        my @users = grep { !defined($_->removed) } $rs->all;
+        $rs->set_cache(\@users);
+        $rs;
+    } else {
+        $rs->search({ removed => undef });
+    }
 }
 
 sub for_report {