Attachment 132171 Details for Bug 23991 – Bug 23991: (QA follow-up) Save some DB queries

[patch] Bug 23991: (QA follow-up) Save some DB queries

Bug-23991-QA-follow-up-Save-some-DB-queries.patch (text/plain), 10.05 KB, created by Nick Clemens (kidclamp) on 2022-03-24 15:21:57 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Nick Clemens (kidclamp)

Created: 2022-03-24 15:21:57 UTC

Size: 10.05 KB

patch

obsolete

>From 14cb420474e89b3d7e60cf17c886422317a1aa5f Mon Sep 17 00:00:00 2001
>From: Tomas Cohen Arazi <tomascohen@theke.io>
>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 <tomascohen@theke.io>
>
>Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
>---
> 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 @@
>             <main>
> 
> <h1>Suggestions</h1>
>-    [% IF suggestions.count %]
>+    [% IF suggestions.size %]
>     <a href="#" id="show_only_mine">Show only mine</a> | <a href="#" id="show_all">Show all suggestions</a>
>     <table id="suggestionst">
>         <thead>
>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 @@
>                     <a class="btn btn-default" id="newsuggestion" href="/cgi-bin/koha/suggestion/suggestion.pl?op=add&amp;suggestedby=[% patron.borrowernumber | html %]&amp;redirect=purchase_suggestions&amp;borrowernumber=[% patron.borrowernumber | html %]"><i class="fa fa-plus"></i> New purchase suggestion</a>
>                 </div>
> 
>-                [% IF suggestions.count %]
>+                [% IF suggestions.size %]
>                   <table id="suggestions">
>                     <thead>
>                         <tr>
>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 %])</a></li>
>+                            ([% suggestion.suggestions.size| html %])</a></li>
> 
>                         [% END # /FOREACH suggestion %]
>                     </ul> 
>@@ -717,7 +717,7 @@
>                 <div id="[% suggestion.suggestiontype | html %]">
>                     <form class="update_suggestions" name="f" method="post" action="/cgi-bin/koha/suggestion/suggestion.pl#[% suggestion.suggestiontype| uri %]">
> 
>-                        [% IF suggestion.suggestions.count %]
>+                        [% IF suggestion.suggestions.size %]
>                             <p>
>                                 <a class="checkall" href="#">Check all</a> | <a class="uncheckall" href="#">Uncheck all</a>
>                             </p>
>@@ -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 6d4f4570f2..edb06297fc 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 ) %]<div class="alert alert-info">The selected suggestions have been deleted.</div>[% END %]
> 
>-                            [% IF suggestions.count OR title_filter %]
>+                            [% IF suggestions.size > 0 OR title_filter %]
>                                 [% SET can_delete_suggestion = 0 %]
>                                 <form action="/cgi-bin/koha/opac-suggestions.pl" class="form-inline" id="search_suggestions_form" method="get">
>                                     <div class="form-row">
>@@ -329,7 +329,7 @@
>                                     </div>
>                                 </form>
>                             [% END %]
>-                            [% IF suggestions.count %]
>+                            [% IF suggestions.size > 0 %]
>                                 [% SET can_delete_suggestion = 0 %]
>                                 <form action="/cgi-bin/koha/opac-suggestions.pl" method="post" id="delete_suggestions">
>                                     <input type="hidden" name="op" value="delete_confirm" />
>@@ -478,7 +478,7 @@
>                                         <p><a class="btn btn-link new" href="/cgi-bin/koha/opac-suggestions.pl?op=add"><i class="fa fa-plus" aria-hidden="true"></i> New purchase suggestion</a></p>
>                                     [% END %]
>                                 [% END %]
>-                            [% END # / IF suggestions.count %]
>+                            [% END # / IF suggestions.size %]
> 
>                         [% END # IF op_else %]
>                     </div> 
>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 0b0b995864..d9a56c3e62 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.30.2

Actions: View | Diff | Splinter Review

Attachments on bug 23991: 95172 | 115203 | 115207 | 116639 | 118454 | 119256 | 127058 | 127100 | 128071 | 128072 | 128073 | 128074 | 128075 | 129511 | 129512 | 129513 | 129523 | 130049 | 131896 | 132026 | 132169 | 132170 | 132171 | 132172 | 132173 | 132174 | 132175 | 133874 | 134922 | 136441 | 136442 | 137201