From: J. David Bavousett <dbavous...@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.h...@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
_______________________________________________
Koha-patches mailing list
Koha-patches@lists.koha-community.org
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-patches
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
