From e3a8f1e20d871fca0b50c57146d2194ffdda00c2 Mon Sep 17 00:00:00 2001 From: Henri-Damien LAURENT Date: Fri, 23 Oct 2009 16:05:33 +0200 Subject: [PATCH] MT 2168 : Logical AND between multiple words strings C4::SQLHelper was doing an implicit OR of multiple words strings It is now fixed and does an AND --- C4/SQLHelper.pm | 15 +++++++++++---- t/db_dependent/SQLHelper.t | 16 +++++++++++----- 2 files changed, 22 insertions(+), 9 deletions(-) diff --git a/C4/SQLHelper.pm b/C4/SQLHelper.pm index 7ee3c70951..08cfdf3956 100644 --- a/C4/SQLHelper.pm +++ b/C4/SQLHelper.pm @@ -334,7 +334,14 @@ sub _filter_fields{ } } else{ - return _filter_string($tablename,$filter_input,$searchtype,$filtercolumns); + my ($keys, $values) = _filter_string($tablename,$filter_input, $searchtype,$filtercolumns); + if ($keys){ + my $stringkey="(".join (") AND (",@$keys).")"; + return [$stringkey],$values; + } + else { + return (); + } } return (\@keys,\@values); @@ -372,8 +379,8 @@ sub _filter_string{ my @columns_filtered= _filter_columns($tablename,$searchtype,$filtercolumns); my $columns= _get_columns($tablename); my (@values,@keys); - my @localkeys; foreach my $operand (@operands){ + my @localkeys; foreach my $field (@columns_filtered){ my ($tmpkeys, $localvalues)=_Process_Operands($operand,"$tablename.$field",$searchtype,$columns); if ($tmpkeys){ @@ -381,9 +388,9 @@ sub _filter_string{ push @localkeys,@$tmpkeys; } } + my $sql= join (' OR ', @localkeys); + push @keys, $sql; } - my $sql= join (' OR ', @localkeys); - push @keys, $sql; if (@keys){ return (\@keys,\@values); diff --git a/t/db_dependent/SQLHelper.t b/t/db_dependent/SQLHelper.t index d3be1f3883..4932a05d5d 100755 --- a/t/db_dependent/SQLHelper.t +++ b/t/db_dependent/SQLHelper.t @@ -5,11 +5,12 @@ use strict; use warnings; -use Data::Dumper; +use YAML; +use C4::Debug; use C4::SQLHelper qw(:all); -use Test::More tests => 14; +use Test::More tests => 15; BEGIN { use_ok('C4::SQLHelper'); @@ -19,21 +20,24 @@ use C4::Branch; my @categories=C4::Category->all; my $branches=C4::Branch->GetBranches; my @branchcodes=keys %$branches; -my $borrid; +my ($borrid, $borrtmp); +$borrtmp=InsertInTable("borrowers",{firstname=>"Jean",surname=>"cocteau",city=>" ",zipcode=>" ",email=>"email",categorycode=>$categories[0]->{categorycode}, branchcode=>$branchcodes[0]}); ok($borrid=InsertInTable("borrowers",{firstname=>"Jean",surname=>"Valjean",city=>" ",zipcode=>" ",email=>"email",categorycode=>$categories[0]->{categorycode}, branchcode=>$branchcodes[0]}),"Insert In Table"); -ok(my $status=UpdateInTable("borrowers",{borrowernumber=>$borrid,firstname=>"Jean",surname=>"Valjean",city=>"ma6tVaCracker ",zipcode=>" ",email=>"email", branchcode=>$branchcodes[1]}),"Update In Table"); +ok(my $status=UpdateInTable("borrowers",{borrowernumber=>$borrid,firstname=>"Jean",surname=>"Valjean",city=>"Dampierre",zipcode=>" ",email=>"email", branchcode=>$branchcodes[1]}),"Update In Table"); my $borrowers=SearchInTable("borrowers"); ok(@$borrowers>0, "Search In Table All values"); $borrowers=SearchInTable("borrowers",{firstname=>"Jean"}); ok(@$borrowers>0, "Search In Table hashref"); $borrowers=SearchInTable("borrowers","Jean"); ok(@$borrowers>0, "Search In Table string"); +eval{$borrowers=SearchInTable("borrowers","Jean Valjean")}; +ok(scalar(@$borrowers)==1 && !($@), "Search In Table does an implicit AND of all the words in strings"); $borrowers=SearchInTable("borrowers",["Valjean",{firstname=>"Jean"}]); ok(@$borrowers>0, "Search In Table arrayref"); $borrowers=SearchInTable("borrowers",["Valjean",{firstname=>"Jean"}],undef,undef,[qw(borrowernumber)]); ok(keys %{$$borrowers[0]} ==1, "Search In Table columns out limit"); $borrowers=SearchInTable("borrowers",["Valjean",{firstname=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(firstname surname title)]); -ok(@$borrowers>0, "Search In Table columns out limit"); +ok(@$borrowers>0, "Search In Table columns out limit to borrowernumber AND filter firstname surname title"); $borrowers=SearchInTable("borrowers",["Valjean",{firstname=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(firstname title)]); ok(@$borrowers==0, "Search In Table columns filter firstname title limit Valjean not in other fields than surname "); $borrowers=SearchInTable("borrowers",["Val",{firstname=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(surname)],"start_with"); @@ -42,5 +46,7 @@ $borrowers=SearchInTable("borrowers",["Val",{firstname=>"Jean"}],undef,undef,[qw ok(@$borrowers==0, "Search In Table columns filter surname Val in exact search not found "); $borrowers=eval{SearchInTable("borrowers",["Val",{member=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(firstname title)],"exact")}; ok(@$borrowers==0 && !($@), "Search In Table fails gracefully when no correct field passed in hash"); + $status=DeleteInTable("borrowers",{borrowernumber=>$borrid}); ok($status>0 && !($@), "DeleteInTable OK"); +$status=DeleteInTable("borrowers",{borrowernumber=>$borrtmp}); -- 2.39.5