From b3c4764bfed04297169027bf3eeb4953f237abbd Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Mon, 12 Apr 2021 12:20:19 +0200 Subject: [PATCH] Bug 17979: Improve SQL query build Take also into account an hypotetical call to BreedingSearch() without parameters --- C4/Breeding.pm | 21 ++++++++++----------- t/db_dependent/Breeding.t | 11 ++++++++++- 2 files changed, 20 insertions(+), 12 deletions(-) diff --git a/C4/Breeding.pm b/C4/Breeding.pm index fe764cfd890..85f52bce562 100644 --- a/C4/Breeding.pm +++ b/C4/Breeding.pm @@ -71,7 +71,7 @@ sub BreedingSearch { my ($search,$isbn) = @_; my $dbh = C4::Context->dbh; my $count = 0; - my ($query,@bind); + my ( $query, @cond, @bind ); my $sth; my @results; @@ -81,21 +81,20 @@ sub BreedingSearch { $query = "SELECT import_record_id, file_name, isbn, title, author FROM import_biblios JOIN import_records USING (import_record_id) - JOIN import_batches USING (import_batch_id) - WHERE "; - @bind=(); + JOIN import_batches USING (import_batch_id)"; + if (defined($search) && length($search)>0) { $search =~ s/(\s+)/\%/g; - $query .= "title like ? OR author like ?"; - push(@bind,"%$search%", "%$search%"); - } - if ($#bind!=-1 && defined($isbn) && length($isbn)>0) { - $query .= " and "; + push @cond, 'title like ? OR author like ?'; + push @bind, "%$search%", "%$search%"; } if (defined($isbn) && length($isbn)>0) { - $query .= "isbn like ?"; - push(@bind,"$isbn%"); + push @cond, 'isbn like ?'; + push @bind, "$isbn%"; } + + $query .= ' WHERE ' . join( ' AND ', @cond ) if @cond; + $sth = $dbh->prepare($query); $sth->execute(@bind); while (my $data = $sth->fetchrow_hashref) { diff --git a/t/db_dependent/Breeding.t b/t/db_dependent/Breeding.t index f3eb54373aa..58c0e6300f2 100755 --- a/t/db_dependent/Breeding.t +++ b/t/db_dependent/Breeding.t @@ -26,7 +26,7 @@ use Modern::Perl; use File::Temp qw/tempfile/; -use Test::More tests => 5; +use Test::More tests => 6; use Test::Warn; use t::lib::Mocks qw( mock_preference ); @@ -84,6 +84,15 @@ subtest ImportBreedingAuth => sub { isnt( $breedingid, $breedingid_2, "For a new record, we get a new id"); }; +subtest 'BreedingSearch' => sub { + plan tests => 2; + + is( C4::Breeding::BreedingSearch(undef, "400638133393x"), 0 ); + + my $count_import_biblios = C4::Context->dbh->selectrow_array(q|SELECT COUNT(*) FROM import_biblios|); + is( C4::Breeding::BreedingSearch(), $count_import_biblios ); +}; + $schema->storage->txn_rollback; #------------------------------------------------------------------------------- -- 2.20.1