From 530c8e0259df290402cd7d69bb0e6cb9b44901ea Mon Sep 17 00:00:00 2001 From: Fridolin Somers Date: Wed, 25 Nov 2015 12:34:18 +0100 Subject: [PATCH] Bug 15252 - Patron search on start with does not work with several terms When searching a patron, search type can be 'start with' and 'contain'. If the search text contains a space (or a coma), this text is splitted into several terms. Actually, the search on 'start with' with several terms never returns a result. It is because the search composes an "AND" SQL query on terms. For example (I display only the surname part) : search type = contain : 'jean paul' => surname like '%jean% AND %paul%' search type = start with : 'jean paul' => surname like 'jean% AND paul%' The query for 'start with' is impossible. I propose, for search with start with, to not split terms : jean paul => surname like 'jean paul%' One can always use '*' to add more truncation : jea* pau* => surname like 'jea% pau%' This bug affects a lot surnames with several terms like 'LE GUELEC' or 'MAC BETH'. Note that the patch moves : $searchmember =~ s/,/ /g; It removes the test "if $searchmember" because $searchmember is tested and set to empty string previously : unless ( $searchmember ) { $searchmember = $dt_params->{sSearch} // ''; } Test plan : ========== - Create two patrons with firstname "Jean Paul" - Go to Patrons module - Choose "Starts with" in "Search type" filter - Perform a search on "Jean Paul" => without patch : you get no result => with this patch : you get the two results - Check you get the two results for search on "Jean Pau" - Check you get the two results for search on "Jea* Pau*" - Check you do not get results for search on "Jea Paul" - Choose "Contains" in "Search type" filter - Check you get the two results for search on "Jean Paul" - Check you get the two results for search on "Jean Pau" - Check you get the two results for search on "Jea* Pau*" - Check you get the two results for search on "Jea Paul" - Check you get the two results for search on "Paul Jean" Signed-off-by: Alex --- C4/Utils/DataTables/Members.pm | 21 +++++++++++++++++---- 1 file changed, 17 insertions(+), 4 deletions(-) diff --git a/C4/Utils/DataTables/Members.pm b/C4/Utils/DataTables/Members.pm index bfa73e0..31af767 100644 --- a/C4/Utils/DataTables/Members.pm +++ b/C4/Utils/DataTables/Members.pm @@ -59,8 +59,6 @@ sub search { push @where_args, $branchcode; } - # split on coma - $searchmember =~ s/,/ /g if $searchmember; my $searchfields = { standard => 'surname,firstname,othernames,cardnumber,userid', email => 'email,emailpro,B_email', @@ -72,14 +70,29 @@ sub search { sort1 => 'sort1', sort2 => 'sort2', }; - foreach my $term ( split / /, $searchmember) { + + # * is replaced with % for sql + $searchmember =~ s/\*/%/g; + + # split into search terms + my @terms; + # consider coma as space + $searchmember =~ s/,/ /g; + if ( $searchtype eq 'contain' ) { + @terms = split / /, $searchmember; + } else { + @terms = ($searchmember); + } + + foreach my $term (@terms) { next unless $term; - $searchmember =~ s/\*/%/g; # * is replaced with % for sql + $term .= '%' # end with anything if $term !~ /%$/; $term = "%$term" # begin with anythin unless start_with if (defined $searchtype) && $searchtype eq "contain" && $term !~ /^%/; + my @where_strs_or; for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) { push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?"; -- 1.7.10.4