From 41f1cf664337602f6543642236b913eafab02711 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Tue, 23 Oct 2018 10:35:24 -0300 Subject: [PATCH] Bug 21635: [sql_modes] Remove GROUP BY clause in batchMod.pl batchMod.pl: DBD::mysql::st execute failed: 'koha_kohadev.authorised_values.authorised_val ue' isn't in GROUP BY [for Statement "SELECT authorised_value, lib FROM authorised_values LEFT JOIN authorised_values_branches ON ( id = av_id ) WHERE category = ? AND ( branchcode = ? OR branchcode IS NULL ) GROUP BY lib ORDER BY lib, lib_opac" with ParamValues: 0='WITHDRAWN', 1="CPL"] at /home/vagrant/kohaclone/tools/batchMod.pl line 396. We must use Koha::AuthorisedValues->search instead of a raw SQL query. Test plan: Edit some items in a batch Confirm that the dropdown list (AV) are correctly filled We will lose speed efficiency here, but better to be consistent, then cache AV in Koha::AuthorisedValues Signed-off-by: Andrew Isherwood Signed-off-by: Marcel de Rooy Signed-off-by: Nick Clemens (cherry picked from commit dab4426457578094eb5aac6531d1a1db631d05c7) Signed-off-by: Martin Renvoize --- tools/batchMod.pl | 17 ++++++----------- 1 file changed, 6 insertions(+), 11 deletions(-) diff --git a/tools/batchMod.pl b/tools/batchMod.pl index 503f5cec63..d7d69f28ce 100755 --- a/tools/batchMod.pl +++ b/tools/batchMod.pl @@ -34,6 +34,7 @@ use C4::Members; use MARC::File::XML; use List::MoreUtils qw/uniq/; +use Koha::AuthorisedValues; use Koha::Biblios; use Koha::DateUtils; use Koha::Items; @@ -300,12 +301,6 @@ if ($op eq "show"){ my @loop_data =(); my $i=0; my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : ""; -my $query = qq{SELECT authorised_value, lib FROM authorised_values}; -$query .= qq{ LEFT JOIN authorised_values_branches ON ( id = av_id ) } if $branch_limit; -$query .= qq{ WHERE category = ?}; -$query .= qq{ AND ( branchcode = ? OR branchcode IS NULL ) } if $branch_limit; -$query .= qq{ GROUP BY lib ORDER BY lib, lib_opac}; -my $authorised_values_sth = $dbh->prepare( $query ); my $libraries = Koha::Libraries->search({}, { order_by => ['branchname'] })->unblessed;# build once ahead of time, instead of multiple times later. @@ -403,10 +398,11 @@ foreach my $tag (sort keys %{$tagslib}) { } else { push @authorised_values, ""; # unless ( $tagslib->{$tag}->{$subfield}->{mandatory} ); - $authorised_values_sth->execute( $tagslib->{$tag}->{$subfield}->{authorised_value}, $branch_limit ? $branch_limit : () ); - while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) { - push @authorised_values, $value; - $authorised_lib{$value} = $lib; + + my @avs = Koha::AuthorisedValues->search({ category => $tagslib->{$tag}->{$subfield}->{authorised_value}, branchcode => $branch_limit }); + for my $av ( @avs ) { + push @authorised_values, $av->authorised_value; + $authorised_lib{$av->authorised_value} = $av->lib; } $value=""; } @@ -494,7 +490,6 @@ foreach my $tag (sort keys %{$tagslib}) { $i++ } } # -- End foreach tag -$authorised_values_sth->finish; -- 2.39.5