NAME

SQL::Abstract::SuperSearch - search a value in many columns

SYNOPSIS

  my ($sql,@bind) = $s->select($table,[ 'listened', 'count(*) as col_count' ],
                          super_search => 'Jamiroquai%',
                          super_search_columns => [qw[artist album track]],
                          where => { mime_type => 'audio/mpeg' },
                          group_by => 'listened',
                          order_by => "col_count desc",
                    );

will generate the SQL statement

  SELECT listened, count(*) as col_count
  FROM files
    WHERE (( mime_type = ? )
       AND ( artist like ?
          OR album like  ?
          OR track like  ? ))
    GROUP BY listened
    ORDER BY col_count desc

and @bind will contain the appropriate bind values:

  # @bind = ('audio/mpeg', 'Jamiroquai%','Jamiroquai%','Jamiroquai%')

CAVEATS

This module adds cross-column searching capabilities. It doesn't take into accoubnt the types of columns, so that's the callers' responsibility.

super_search VALUE, COLUMNS, WHERE

This is the core implementation. See the synopsis for some example.

$sql->select TABLE, COLUMNS, %CLAUSES

Returns the SQL statement and bind values for the clauses. This is different from how SQL::Abstract does it. The code tries to recognize old usage and fall back to SQL::Abstract, but the fallback doesn't always work.

  my ($sql,@bind) = $s->select('files',[ 'listened', 'count(*) as col_count' ],
                      where => { artist => 'Jamiroquai', mime_type => 'audio/mpeg' },
                      group_by => 'listened',
                      order_by => "col_count desc",
                      having  => { col_count => {'<' => 5 }},

AUTHOR

Max Maischein, <corion@cpan.org>

SEE ALSO

SQL::Abstract, SQL::Abstract::Clauses, SQL::Abstract::Limit