Only request needed join data in places.

The export flow and the admin search request more data
from the database than they need, which slows it down.

5 files changed, 16 insertions, 7 deletions

diff --git a/CHANGELOG.md b/CHANGELOG.md
index 3fb3a88ed..bcaf66717 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -5,6 +5,7 @@
         - order unsent reports by confirmed date
         - Disable staff private tickbox on new reports if category is private. #2961
         - Move stats from main admin index to stats index.
+        - Speed up dashboard export and report search.
     - Bugfixes
         - Application user in Docker container can't install packages. #2914
         - Look at all categories when sending reports.
diff --git a/perllib/FixMyStreet/App/Controller/Admin/Reports.pm b/perllib/FixMyStreet/App/Controller/Admin/Reports.pm
index ef9736ff6..7300fe676 100644
--- a/perllib/FixMyStreet/App/Controller/Admin/Reports.pm
+++ b/perllib/FixMyStreet/App/Controller/Admin/Reports.pm
@@ -108,7 +108,8 @@ sub index : Path {
         my $problems = $c->cobrand->problems->search(
             $query,
             {
-                prefetch => 'user',
+                join => 'user',
+                '+columns' => 'user.email',
                 rows => 50,
                 order_by => $order,
             }
@@ -150,8 +151,9 @@ sub index : Path {
                     -or => $query,
                 },
                 {
-                    -select   => [ 'me.*', qw/problem.bodies_str problem.state/ ],
-                    prefetch => [qw/user problem/],
+                    '+columns' => ['user.email'],
+                    join => 'user',
+                    prefetch => [qw/problem/],
                     rows => 50,
                     order_by => { -desc => 'me.id' }
                 }
diff --git a/perllib/FixMyStreet/App/Controller/Dashboard.pm b/perllib/FixMyStreet/App/Controller/Dashboard.pm
index 27c8630f0..ad6c9ba98 100644
--- a/perllib/FixMyStreet/App/Controller/Dashboard.pm
+++ b/perllib/FixMyStreet/App/Controller/Dashboard.pm
@@ -349,7 +349,8 @@ sub export_as_csv : Private {
 
     my $csv = $c->stash->{csv} = {
         objects => $c->stash->{objects_rs}->search_rs({}, {
-            prefetch => 'comments',
+            join => 'comments',
+            '+columns' => ['comments.problem_state', 'comments.state', 'comments.confirmed', 'comments.mark_fixed'],
             order_by => ['me.confirmed', 'me.id'],
             cursor_page_size => 1000,
         }),
diff --git a/perllib/FixMyStreet/Cobrand/BathNES.pm b/perllib/FixMyStreet/Cobrand/BathNES.pm
index 6a4f1c00b..06095734b 100644
--- a/perllib/FixMyStreet/Cobrand/BathNES.pm
+++ b/perllib/FixMyStreet/Cobrand/BathNES.pm
@@ -194,7 +194,7 @@ sub dashboard_export_updates_add_columns {
 
     $c->stash->{csv}->{objects} = $c->stash->{csv}->{objects}->search(undef, {
         '+columns' => ['user.email'],
-        prefetch => 'user',
+        join => 'user',
     });
     my $user_lookup = $self->_dashboard_user_lookup;
 
@@ -237,7 +237,7 @@ sub dashboard_export_problems_add_columns {
 
     $c->stash->{csv}->{objects} = $c->stash->{csv}->{objects}->search(undef, {
         '+columns' => ['user.email', 'user.phone'],
-        prefetch => 'user',
+        join => 'user',
     });
     my $user_lookup = $self->_dashboard_user_lookup;
 
diff --git a/perllib/FixMyStreet/Cobrand/TfL.pm b/perllib/FixMyStreet/Cobrand/TfL.pm
index 8e133fa58..b98ad1d8b 100644
--- a/perllib/FixMyStreet/Cobrand/TfL.pm
+++ b/perllib/FixMyStreet/Cobrand/TfL.pm
@@ -293,7 +293,12 @@ sub dashboard_export_problems_add_columns {
 
         my $change = $report->admin_log_entries->search(
             { action => 'category_change' },
-            { prefetch => 'user', rows => 1, order_by => { -desc => 'me.id' } }
+            {
+                join => 'user',
+                '+columns' => ['user.name'],
+                rows => 1,
+                order_by => { -desc => 'me.id' }
+            }
         )->single;
         my $reassigned_at = $change ? $change->whenedited : '';
         my $reassigned_by = $change ? $change->user->name : '';