Use raw cursor to fetch reports.

2 files changed, 30 insertions, 23 deletions

diff --git a/perllib/FixMyStreet/App/Controller/Reports.pm b/perllib/FixMyStreet/App/Controller/Reports.pm
index 76a2a2aeb..a885bb13d 100644
--- a/perllib/FixMyStreet/App/Controller/Reports.pm
+++ b/perllib/FixMyStreet/App/Controller/Reports.pm
@@ -2,6 +2,7 @@ package FixMyStreet::App::Controller::Reports;
 use Moose;
 use namespace::autoclean;
 
+use List::MoreUtils qw(zip);
 use POSIX qw(strcoll);
 use mySociety::MaPit;
 use mySociety::VotingArea;
@@ -245,40 +246,46 @@ sub load_and_group_problems : Private {
     my $where = {
         state => [ 'confirmed', 'fixed' ]
     };
-    my @cols = ( 'id', 'council', 'state', 'areas' );
+    my @extra_cols = ();
     if ($c->stash->{ward}) {
         $where->{areas} = { 'like', '%' . $c->stash->{ward}->{id} . '%' }; # FIXME Check this is secure
-        push @cols, 'title', 'detail';
+        push @extra_cols, 'title', 'detail';
     } elsif ($c->stash->{council}) {
         $where->{areas} = { 'like', '%' . $c->stash->{council}->{id} . '%' };
-        push @cols, 'title', 'detail';
+        push @extra_cols, 'title', 'detail';
     }
     my $problems = $c->cobrand->problems->search(
         $where,
         {
             columns => [
-                @cols,
+                'id', 'council', 'state', 'areas',
                 { duration => { extract => "epoch from current_timestamp-lastupdate" } },
                 { age      => { extract => "epoch from current_timestamp-confirmed"  } },
+                @extra_cols,
             ],
             order_by => { -desc => 'id' },
         }
     );
+    $problems = $problems->cursor; # Raw DB cursor for speed
 
     my ( %fixed, %open );
     my $re_councils = join('|', keys %{$c->stash->{areas_info}});
-    while ( my $row = $problems->next ) {
-        if (!$row->council) {
+    my @cols = ( 'id', 'council', 'state', 'areas', 'duration', 'age', 'title', 'detail' );
+    while ( my @problem = $problems->next ) {
+        my %problem = zip @cols, @problem;
+        if ( !$problem{council} ) {
             # Problem was not sent to any council, add to possible councils
-            my $areas = $row->areas;
-            while ($areas =~ /,($re_councils)(?=,)/g) {
-                add_row($row, $1, \%fixed, \%open);
+            while ($problem{areas} =~ /,($re_councils)(?=,)/g) {
+                add_row( \%problem, $1, \%fixed, \%open );
             }
         } else {
             # Add to councils it was sent to
-            foreach (@{ $row->councils }) {
+            (my $council = $problem{council}) =~ s/\|.*$//;
+            my @council = split( /,/, $council );
+            $problem{councils} = scalar @council;
+            foreach ( @council ) {
                 next if $c->stash->{council} && $_ != $c->stash->{council}->{id};
-                add_row($row, $_, \%fixed, \%open);
+                add_row( \%problem, $_, \%fixed, \%open );
             }
         }
     }
@@ -297,11 +304,11 @@ sub sort_problems : Private {
     my $open = $c->stash->{open};
 
     foreach (qw/new old/) {
-        $c->stash->{fixed}{$id}{$_} = [ sort { $a->get_column('duration') <=> $b->get_column('duration') } @{$fixed->{$id}{$_}} ]
+        $c->stash->{fixed}{$id}{$_} = [ sort { $a->{duration} <=> $b->{duration} } @{$fixed->{$id}{$_}} ]
             if $fixed->{$id}{$_};
     }
     foreach (qw/new older unknown/) {
-        $c->stash->{open}{$id}{$_} = [ sort { $a->get_column('age') <=> $b->get_column('age') } @{$open->{$id}{$_}} ]
+        $c->stash->{open}{$id}{$_} = [ sort { $a->{age} <=> $b->{age} } @{$open->{$id}{$_}} ]
             if $open->{$id}{$_};
     }
 }
@@ -323,17 +330,17 @@ sub redirect_area : Private {
     $c->res->redirect( $c->uri_for($url) );
 }
 
+my $fourweeks = 4*7*24*60*60;
 sub add_row {
-    my ($row, $council, $fixed, $open) = @_;
-    my $fourweeks = 4*7*24*60*60;
-    my $duration = ($row->get_column('duration') > 2 * $fourweeks) ? 'old' : 'new';
-    my $type = ($row->get_column('duration') > 2 * $fourweeks)
+    my ( $problem, $council, $fixed, $open ) = @_;
+    my $duration_str = ( $problem->{duration} > 2 * $fourweeks ) ? 'old' : 'new';
+    my $type = ( $problem->{duration} > 2 * $fourweeks )
         ? 'unknown'
-        : ($row->get_column('age') > $fourweeks ? 'older' : 'new');
+        : ($problem->{age} > $fourweeks ? 'older' : 'new');
     # Fixed problems are either old or new
-    push @{$fixed->{$council}{$duration}}, $row if $row->state eq 'fixed';
+    push @{$fixed->{$council}{$duration_str}}, $problem if $problem->{state} eq 'fixed';
     # Open problems are either unknown, older, or new
-    push @{$open->{$council}{$type}}, $row if $row->state eq 'confirmed';
+    push @{$open->{$council}{$type}}, $problem if $problem->{state} eq 'confirmed';
 }
 
 =head1 AUTHOR
diff --git a/templates/web/default/reports/council.html b/templates/web/default/reports/council.html
index 4067d0619..1c35481e7 100755
--- a/templates/web/default/reports/council.html
+++ b/templates/web/default/reports/council.html
@@ -84,10 +84,10 @@
 
 <ul>
 [% FOREACH problem IN problems %]
-    <li><a href="[% c.uri_for(problem.url) %]">[% problem.title | html %]</a>
-    [% IF problem.councils.size > 1 %] <small>[% loc('(sent to both)') %]</small> [% END %]
+    <li><a href="[% c.uri_for('/report/' _ problem.id) %]">[% problem.title | html %]</a>
+    [% IF problem.councils > 1 %] <small>[% loc('(sent to both)') %]</small> [% END %]
     [% IF c.cobrand.moniker != 'emptyhomes' %]
-        [% IF problem.councils.size == 0 %] <small>[% loc('(not sent to council)') %]</small> [% END %]
+        [% IF problem.councils == 0 %] <small>[% loc('(not sent to council)') %]</small> [% END %]
     [% END %]
     [% IF all %] <br><small>[% problem.detail %]</small> [% END %]
     </li>