[Koha-patches] [PATCH] [SIGNED-OFF] Bug 4340 - patron name search does not handle punctuated names

[guillaume.hatt at enc.sorbonne.fr]

From: J. David Bavousett <dbavousett at ptfs.com>

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 <guillaume.hatt at enc.sorbonne.fr>
---
 C4/Members.pm |   26 ++++++++++++++++----------
 1 files changed, 16 insertions(+), 10 deletions(-)

diff --git a/C4/Members.pm b/C4/Members.pm
index b9391a9..2d915ad 100644
--- a/C4/Members.pm
+++ b/C4/Members.pm
@@ -208,22 +208,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 - .= <<EOT;
         }
-- 
1.5.6.5