From 82405f0ee06969ed15cac89c86dc1c11c9527342 Mon Sep 17 00:00:00 2001 From: Tomas Cohen Arazi Date: Mon, 29 Nov 2021 10:17:51 -0300 Subject: [PATCH] Bug 23991: (QA follow-up) Save some DB queries This patch makes the suggestion-related pages rely on array size instead of querying the DB each time they need to. In the case of suggestion/suggestion.pl it goes from 4 COUNT(*) to 1. To test, with KTD: 1. Run on the host machine: $ docker exec -ti koha_db_1 bash $ mysql -ppassword > SET GLOBAL general_log_file='/var/log/mysql/mycustom.log'; > SET GLOBAL log_output = 'FILE'; > SET GLOBAL general_log = 'ON'; > \q $ tail -f /var/log/mysql/mycustom.log | grep suggestions 2. Visit the different pages changed on this bug => SUCCESS: Some queries 3. Apply this patch 4. Repeat 2 => SUCCESS: Less queries! 5. Sign off :-D Signed-off-by: Tomas Cohen Arazi --- acqui/newordersuggestion.pl | 4 ++-- .../en/modules/acqui/newordersuggestion.tt | 2 +- .../modules/members/purchase-suggestions.tt | 2 +- .../prog/en/modules/suggestion/suggestion.tt | 6 +++--- .../bootstrap/en/modules/opac-suggestions.tt | 6 +++--- members/purchase-suggestions.pl | 5 ++++- opac/opac-suggestions.pl | 4 ++-- suggestion/suggestion.pl | 20 +++++++++++-------- 8 files changed, 28 insertions(+), 21 deletions(-) diff --git a/acqui/newordersuggestion.pl b/acqui/newordersuggestion.pl index b6c2bda284..9d35ec48de 100755 --- a/acqui/newordersuggestion.pl +++ b/acqui/newordersuggestion.pl @@ -128,7 +128,7 @@ if ( $op eq 'connectDuplicate' ) { ConnectSuggestionAndBiblio( $suggestionid, $duplicateNumber ); } -my $suggestions = Koha::Suggestions->search_limited( +my $suggestions = [ Koha::Suggestions->search_limited( { ( $author ? ( author => $author ) : () ), ( $title ? ( title => $title ) : () ), @@ -136,7 +136,7 @@ my $suggestions = Koha::Suggestions->search_limited( STATUS => 'ACCEPTED' }, { prefetch => ['managedby', 'suggestedby'] }, -); +)->as_list ]; my $vendor = Koha::Acquisition::Booksellers->find( $booksellerid ); $template->param( diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/newordersuggestion.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/newordersuggestion.tt index 737c0d3c82..278984d2c7 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/newordersuggestion.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/newordersuggestion.tt @@ -41,7 +41,7 @@

Suggestions

- [% IF suggestions.count %] + [% IF suggestions.size %] Show only mine | Show all suggestions diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/members/purchase-suggestions.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/members/purchase-suggestions.tt index fb956e8cad..8a2661aad5 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/members/purchase-suggestions.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/members/purchase-suggestions.tt @@ -42,7 +42,7 @@ New purchase suggestion - [% IF suggestions.count %] + [% IF suggestions.size %]
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/suggestion/suggestion.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/suggestion/suggestion.tt index ff04d6d753..1443862f8c 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/suggestion/suggestion.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/suggestion/suggestion.tt @@ -707,7 +707,7 @@ No name [% END %] [% END %] - ([% suggestion.suggestions.count| html %]) + ([% suggestion.suggestions.size| html %]) [% END # /FOREACH suggestion %] @@ -717,7 +717,7 @@
- [% IF suggestion.suggestions.count %] + [% IF suggestion.suggestions.size %]

Check all | Uncheck all

@@ -1310,7 +1310,7 @@ columns_settings = [% TablesSettings.GetColumns( 'acqui', 'suggestions', 'suggestions', 'json' ) | $raw %] [% FOREACH suggestion IN suggestions %] - [% IF suggestion.suggestions.count %] + [% IF suggestion.suggestions.size %] KohaTable("table_[% loop.count| html %]", { "sorting": [[ 3, "asc" ]], "autoWidth": false, diff --git a/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-suggestions.tt b/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-suggestions.tt index 3e549e7335..7bd2f187b8 100644 --- a/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-suggestions.tt +++ b/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-suggestions.tt @@ -301,7 +301,7 @@ [% IF ( deleted ) %]
The selected suggestions have been deleted.
[% END %] - [% IF suggestions.count OR title_filter %] + [% IF suggestions.size > 0 OR title_filter %] [% SET can_delete_suggestion = 0 %]
@@ -329,7 +329,7 @@
[% END %] - [% IF suggestions.count %] + [% IF suggestions.size > 0 %] [% SET can_delete_suggestion = 0 %]
@@ -478,7 +478,7 @@

New purchase suggestion

[% END %] [% END %] - [% END # / IF suggestions.count %] + [% END # / IF suggestions.size %] [% END # IF op_else %]
diff --git a/members/purchase-suggestions.pl b/members/purchase-suggestions.pl index 0bf5103797..1739e841ae 100755 --- a/members/purchase-suggestions.pl +++ b/members/purchase-suggestions.pl @@ -48,7 +48,10 @@ $template->param( suggestionsview => 1, ); -my $suggestions = Koha::Suggestions->search_limited( { suggestedby => $borrowernumber }, { prefetch => 'managedby' } ); +my $suggestions = [ + Koha::Suggestions->search_limited( { suggestedby => $borrowernumber }, + { prefetch => 'managedby' } )->as_list +]; $template->param( suggestions => $suggestions ); diff --git a/opac/opac-suggestions.pl b/opac/opac-suggestions.pl index 3949f06c97..e31c5b619a 100755 --- a/opac/opac-suggestions.pl +++ b/opac/opac-suggestions.pl @@ -211,7 +211,7 @@ if ( $op eq "add_confirm" ) { $op = 'else'; } -my $suggestions = Koha::Suggestions->search_limited( +my $suggestions = [ Koha::Suggestions->search_limited( { $suggestion->{suggestedby} ? ( suggestedby => $suggestion->{suggestedby} ) @@ -220,7 +220,7 @@ my $suggestions = Koha::Suggestions->search_limited( ? ( title => $title_filter ) : (), } -); +)->as_list ]; if ( $op eq "delete_confirm" ) { my @delete_field = $input->multi_param("delete_field"); diff --git a/suggestion/suggestion.pl b/suggestion/suggestion.pl index eaae9ac0d1..30cb57d367 100755 --- a/suggestion/suggestion.pl +++ b/suggestion/suggestion.pl @@ -397,14 +397,18 @@ if ($op=~/else/) { } } } - my $suggestions = Koha::Suggestions->search_limited( { %$suggestion_ref, archived => $filter_archived } ); - - push @allsuggestions,{ - "suggestiontype"=>$criteriumvalue||"suggest", - "suggestiontypelabel"=>GetCriteriumDesc($criteriumvalue,$displayby)||"", - 'suggestions' => $suggestions, - 'reasonsloop' => $reasonsloop, - } if $suggestions->count; + my @suggestions = + Koha::Suggestions->search_limited( + { %$suggestion_ref, archived => $filter_archived } )->as_list; + + push @allsuggestions, + { + "suggestiontype" => $criteriumvalue || "suggest", + "suggestiontypelabel" => GetCriteriumDesc( $criteriumvalue, $displayby ) || "", + 'suggestions' => \@suggestions, + 'reasonsloop' => $reasonsloop, + } + if scalar @suggestions > 0; delete $$suggestion_ref{$displayby} unless $definedvalue; } -- 2.32.0