From d29fd92f66bba11ee903aaf38da737e711b9e9b1 Mon Sep 17 00:00:00 2001 From: "J. David Bavousett" Date: Tue, 5 Apr 2011 16:26:41 +0200 Subject: [PATCH] Bug 4340 - patron name search does not handle punctuated names This patch will enable C4::Members::SearchMember to handle searching for punctuated names (e.g. the "Jones" in "Smith-Jones" or the "Angelo" in "D'Angelo") It is possible to add a bunch of LIKE clauses, but that adds to the search time in a rather dramatic way. REGEXP, by itself, is also a performance killer, but I found a suggestion of using a more-general LIKE ANDed with the specific REGEXP, and this gives excellent performance over other approaches. Signed-off-by: Guillaume Hatt Signed-off-by: Chris Cormack --- C4/Members.pm | 26 ++++++++++++++++---------- 1 file changed, 16 insertions(+), 10 deletions(-) diff --git a/C4/Members.pm b/C4/Members.pm index bf73e0e906..f0ac280c3b 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -213,22 +213,28 @@ sub SearchMember { $query.=" borrowers.branchcode =".$dbh->quote(C4::Context->userenv->{'branch'})." AND " unless (C4::Context->userenv->{'branch'} eq "insecure"); } } - $query.="((surname LIKE ? OR surname LIKE ? - OR firstname LIKE ? OR firstname LIKE ? - OR othernames LIKE ? OR othernames LIKE ?) + $query.="((surname LIKE ? OR (surname LIKE ? AND surname REGEXP ?) + OR firstname LIKE ? OR (firstname LIKE ? AND firstname REGEXP ?) + OR othernames LIKE ? OR (othernames LIKE ? AND othernames REGEXP ?)) " . ($category_type?" AND category_type = ".$dbh->quote($category_type):""); + my $regex = '[[:punct:][:space:]]'.$data[0]; @bind = ( - "$data[0]%", "% $data[0]%", "$data[0]%", "% $data[0]%", - "$data[0]%", "% $data[0]%" + "$data[0]%", "%$data[0]%", $regex, + "$data[0]%", "%$data[0]%", $regex, + "$data[0]%", "%$data[0]%", $regex ); for ( my $i = 1 ; $i < $count ; $i++ ) { - $query = $query . " AND (" . " surname LIKE ? OR surname LIKE ? - OR firstname LIKE ? OR firstname LIKE ? - OR othernames LIKE ? OR othernames LIKE ?)"; + $query = $query . " AND (" . " surname LIKE ? OR (surname LIKE ? AND surname REGEXP ?) + OR firstname LIKE ? OR (firstname LIKE ? AND firstname REGEXP ?) + OR othernames LIKE ? OR (othernames LIKE ? AND othernames REGEXP ?))"; + $regex = '[[:punct:][:space:]]'.$data[$i]; push( @bind, - "$data[$i]%", "% $data[$i]%", "$data[$i]%", - "% $data[$i]%", "$data[$i]%", "% $data[$i]%" ); + "$data[$i]%", "%$data[$i]%", $regex, + "$data[$i]%", "%$data[$i]%", $regex, + "$data[$i]%", "%$data[$i]%", $regex + ); + # FIXME - .= <