SQL::KeywordSearch - generate SQL for simple keyword searches
use SQL::KeywordSearch; my ($search_sql,@bind) = sql_keyword_search( keywords => 'cat,brown,whiskers', columns => ['pets','colors','names'] ); my $sql = "SELECT title from articles WHERE user_id = 5 AND ".$search_sql;
The solution provided here is simple, suitable for relatively small numbers of rows and columns. It is also simple-minded in that it can't sort the results based on their relevance.
For large data sets and more features, a full-text indexing and searching solution is recommended to be used instead. Tsearch2 for PostgreSQL, http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ is one such solution.
This module was developed for use with PostgreSQL. It can work with other databases by specifying the regular expression operator to use. The 'REGEXP' operator should work for MySQL.
Since a regular expression for word boundary checking is about the only fancy database feature we used, other databases should work as well.
($sql,@bind) = sql_keyword_search(...); (@interp) = sql_keyword_search(interp => 1, ...);
sql_keyword_search builds a sql statement based on a keyword field containing a list of comma, space, semicolon or colon separated keywords. This prepares a case-insensitive regular expression search.
($sql, @bind) = sql_keyword_search( keywords => 'cat,brown', columns => ['pets','colors'], every_column => 1, every_word => 1, whole_word => 1, operator => 'REGEXP' );
Now the result would look like:
$sql = qq{( (lower(pets) ~ lower(?) OR lower(colors) ~ lower(?) ) OR (lower(pets) ~ lower(?) OR lower(colors) ~ lower(?) ))}; @bind = ('cat','cat','brown','brown');
You can control the use of AND, OR and other aspects of the SQL generation through the options below.
A string of comma,space,semicolon or color separated keywords. Required.
An anonymous array of columns to perform the keyword search on. Required.
If you would like all words to match in all columns, you set this to 1.
By default, words can match in one or more columns.
If you would like all words to match in particular column for it to be considered a match, set this value to 1
By default, one or more words can match in a particular column.
Set this to true to do only match against whole words. A substring search is the default.
Set to 'REGEXP' if you are using MySQL. The default works for PostgreSQL.
# integrate with DBIx::Interp my $articles = $dbx->selectall_arrayref_i(" SELECT article_id, title, summary FROM articles WHERE ", sql_keyword_search( keywords => $q->param('q'), columns => [qw/title summary/] interp => 1, ) ,attr(Slice=>{}));
Turn this on to return an array of SQL like SQL::Interp or DBIx::Interp expect as input.
mark@summersault.com, <mark at summersault.com>
<mark at summersault.com>
Please report any bugs or feature requests to bug-sql-keywordsearch at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=SQL-KeywordSearch. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.
bug-sql-keywordsearch at rt.cpan.org
search.cpan.org
http://search.cpan.org/dist/SQL-KeywordSearch
Copyright 2006 - 2009 Mark Stosberg, <mark@summersault.com>, all rights reserved.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
To install SQL::KeywordSearch, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::KeywordSearch
CPAN shell
perl -MCPAN -e shell install SQL::KeywordSearch
For more information on module installation, please visit the detailed CPAN module installation guide.