File Coverage

blib/lib/SQL/KeywordSearch.pm

Criterion	Covered	Total	%
statement	36	37	97.3
branch	14	16	87.5
condition			n/a
subroutine	5	5	100.0
pod	1	1	100.0
total	56	59	94.9

package SQL::KeywordSearch;
use Params::Validate ':all';
use base 'Exporter';

our $VERSION = 1.13;

# Make the functions available by default
our @EXPORT = qw(
  sql_keyword_search
);

use warnings;
use strict;

=head1 NAME

SQL::KeywordSearch - generate SQL for simple keyword searches


=head1 SYNOPSIS

  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;

=head1 About keyword searching

The solution provided here is I, suitable for relatively
small numbers of rows and columns. It is also simple-minded in that
it I 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,
L is one such solution.

=head1 Database Support

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.

=head1 Functions

=head2 sql_keyword_search()

 ($sql,@bind) = sql_keyword_search(...);
 (@interp)    = sql_keyword_search(interp => 1, ...);

B 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.

=over 4

=item B

A string of comma,space,semicolon or color separated keywords. Required.

=item B

An anonymous array of columns to perform the keyword search on. Required.

=item B (default: false)

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.

=item B (default: false)

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.

=item B (default: false)

Set this to true to do only match against whole words. A substring search is
the default.

=item B (default: ~)

Set to 'REGEXP' if you are using MySQL. The default works for PostgreSQL.

=item B (default: off)

    # 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 L or
L expect as input.

=back

=cut

sub sql_keyword_search {
    my %p = validate(@_,{
        keywords     => { type => SCALAR },
        every_column => { default => 0 },
        every_word   => { default => 0 },
        whole_word   => { default => 0 },
        columns      => { type => ARRAYREF },
        operator     => { default => '~' },
        interp       => { default => 0 },
    });

    my (@sql,@bind);

    my @list = split /[\s\,\;\:]+/ , $p{keywords};
    my @columns = @{ $p{columns} };

    push @sql, "(\n";
    foreach (my $j = 0; $j <= $#list; $j++) {
        push @sql, "(";
        foreach  (my $i = 0; $i <= $#columns; $i ++) {
            my $word = $list[$j];
            if (defined $word) {
                if ($p{whole_word}) {
                    $word = "(^|[[:<:]])".$word.'([[:>:]]|$)';
                }
                push @sql, "lower($columns[$i]) $p{operator} ";
                if ($p{interp}) {
                    push @sql, "lower(",\$word,")";
                }
                else {
                    push @sql, "lower(?)\n";
                    push @bind, $word;
                }
            }
            push @sql, " ".($p{every_column} ? 'AND' : 'OR' )." " if $i != $#columns;
        }
        push @sql, ")";
        push @sql, "\n ".($p{every_word} ? 'AND' : 'OR' )." \n" if $j != $#list;
    }
    push @sql, "\n)\n";

    if ($p{interp}) {
        return @sql,
    }
    else {
        return ((join '', @sql),@bind);
    }
}

1;

__END__