From 683b188b288fe43526e1649c784fa44435559655 Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Thu, 30 Apr 2020 13:51:42 +0100 Subject: Move per-row Contact lookup to the database. On admin report lists, and in front-end lists when an inspector, each row was querying the database for `category_display`. We create a new relationship for this query, and join/prefetch it wherever we request this data. Include staff joins on /around page, copying what happens on /reports to prevent more lookups there too. Also add some joins for user email in admin report list. --- perllib/FixMyStreet/DB/ResultSet/Problem.pm | 30 ++++++++++++++++++++--------- 1 file changed, 21 insertions(+), 9 deletions(-) (limited to 'perllib/FixMyStreet/DB/ResultSet/Problem.pm') diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index e23cf78e1..2ce4c04be 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -26,30 +26,31 @@ sub body_query { # Edits PARAMS in place to either hide non_public reports, or show them # if user is superuser (all) or inspector (correct body) sub non_public_if_possible { - my ($rs, $params, $c) = @_; + my ($rs, $params, $c, $table) = @_; + $table ||= 'me'; if ($c->user_exists) { my $only_non_public = $c->stash->{only_non_public} ? 1 : 0; if ($c->user->is_superuser) { # See all reports, no restriction - $params->{non_public} = 1 if $only_non_public; + $params->{"$table.non_public"} = 1 if $only_non_public; } elsif ($c->user->has_body_permission_to('report_inspect') || $c->user->has_body_permission_to('report_mark_private')) { if ($only_non_public) { $params->{'-and'} = [ - non_public => 1, + "$table.non_public" => 1, $rs->body_query($c->user->from_body->id), ]; } else { $params->{'-or'} = [ - non_public => 0, + "$table.non_public" => 0, $rs->body_query($c->user->from_body->id), ]; } } else { - $params->{non_public} = 0; + $params->{"$table.non_public"} = 0; } } else { - $params->{non_public} = 0; + $params->{"$table.non_public"} = 0; } } @@ -175,22 +176,33 @@ sub around_map { my ( $rs, $c, %p) = @_; my $attr = { order_by => $p{order}, + rows => $c->cobrand->reports_per_page, }; - $attr->{rows} = $c->cobrand->reports_per_page; + if ($c->user_exists) { + if ($c->user->from_body || $c->user->is_superuser) { + push @{$attr->{prefetch}}, 'contact'; + } + if ($c->user->has_body_permission_to('planned_reports')) { + push @{$attr->{prefetch}}, 'user_planned_reports'; + } + if ($c->user->has_body_permission_to('report_edit_priority') || $c->user->has_body_permission_to('report_inspect')) { + push @{$attr->{prefetch}}, 'response_priority'; + } + } unless ( $p{states} ) { $p{states} = FixMyStreet::DB::Result::Problem->visible_states(); } my $q = { - state => [ keys %{$p{states}} ], + 'me.state' => [ keys %{$p{states}} ], latitude => { '>=', $p{min_lat}, '<', $p{max_lat} }, longitude => { '>=', $p{min_lon}, '<', $p{max_lon} }, }; $q->{$c->stash->{report_age_field}} = { '>=', \"current_timestamp-'$p{report_age}'::interval" } if $p{report_age}; - $q->{category} = $p{categories} if $p{categories} && @{$p{categories}}; + $q->{'me.category'} = $p{categories} if $p{categories} && @{$p{categories}}; $rs->non_public_if_possible($q, $c); -- cgit v1.2.3 From 04d217ae31c156c17d95e757842693caa31c06ed Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Tue, 16 Jun 2020 20:36:52 +0100 Subject: [Hounslow] Closed as well as fixed on front page. --- perllib/FixMyStreet/DB/ResultSet/Problem.pm | 17 +++++++++++++++-- 1 file changed, 15 insertions(+), 2 deletions(-) (limited to 'perllib/FixMyStreet/DB/ResultSet/Problem.pm') diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index 2ce4c04be..9f7c035ed 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -72,13 +72,26 @@ sub _cache_timeout { FixMyStreet->config('CACHE_TIMEOUT') // 3600; } +sub recent_completed { + my $rs = shift; + $rs->_recent_in_states('completed', [ + FixMyStreet::DB::Result::Problem->fixed_states(), + FixMyStreet::DB::Result::Problem->closed_states() + ]); +} + sub recent_fixed { my $rs = shift; - my $key = "recent_fixed:$site_key"; + $rs->_recent_in_states('fixed', [ FixMyStreet::DB::Result::Problem->fixed_states() ]); +} + +sub _recent_in_states { + my ($rs, $state_key, $states) = @_; + my $key = "recent_$state_key:$site_key"; my $result = Memcached::get($key); unless ($result) { $result = $rs->search( { - state => [ FixMyStreet::DB::Result::Problem->fixed_states() ], + state => $states, lastupdate => { '>', \"current_timestamp-'1 month'::interval" }, } )->count; Memcached::set($key, $result, _cache_timeout()); -- cgit v1.2.3 From bd42d6a1fcb6c8e6d89413e0ee22617625d95bad Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Wed, 29 Jul 2020 09:03:56 +0100 Subject: Add database index for full text search. --- perllib/FixMyStreet/DB/ResultSet/Problem.pm | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'perllib/FixMyStreet/DB/ResultSet/Problem.pm') diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index 9f7c035ed..3ac9187ee 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -8,6 +8,12 @@ use Memcached; use mySociety::Locale; use FixMyStreet::DB; +use Moo; +with 'FixMyStreet::Roles::FullTextSearch'; +__PACKAGE__->load_components('Helper::ResultSet::Me'); +sub text_search_columns { qw(id external_id bodies_str name title detail) } +sub text_search_nulls { qw(external_id bodies_str) } + my $site_key; sub set_restriction { -- cgit v1.2.3 From 7af4f2cc87cd6ff55501bb2856193a03fe72158c Mon Sep 17 00:00:00 2001 From: M Somerville Date: Wed, 5 Aug 2020 15:56:10 +0100 Subject: Add database index for user full text search. --- perllib/FixMyStreet/DB/ResultSet/Problem.pm | 1 + 1 file changed, 1 insertion(+) (limited to 'perllib/FixMyStreet/DB/ResultSet/Problem.pm') diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index 3ac9187ee..a7c365c1e 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -13,6 +13,7 @@ with 'FixMyStreet::Roles::FullTextSearch'; __PACKAGE__->load_components('Helper::ResultSet::Me'); sub text_search_columns { qw(id external_id bodies_str name title detail) } sub text_search_nulls { qw(external_id bodies_str) } +sub text_search_translate { '/.' } my $site_key; -- cgit v1.2.3