SQL::Filter - Generate complex SQL where from Perl data structures
use SQL::Filter; my $filter = SQL::Filter->new( table => 'testme t', field => '*', filter => [ # or subclass and return them from get_filter method { tables => [ [ 'another_test a' ] ], # natural join that field => 'field', on_true => { where => { 'a.field' => { -like => '$field' }, }, }, }, { field => 'another_field', cond => { first_value => { where => { 'a.first_value' => { -not_like => 'first_value' }, }, }, }, }, ], input => { field => 'value', another_field => 'first_value', }, ); my ($stmt, @bind) = $filter->select;
Making filter queries from complicated database always was pain in ass. So, after attempting to patch such code, I decided to write this module, which in exactly can be treated like an extension to SQL::Abstract which it uses.
Now there is no need to put a bunch of ifs and $sql .= '...' statements in your perl code. All you need to do a test is there, in that module.
$sql .= '...'
Filters are build like mentoined in example from "SYNOPSIS" section.
my @filter = ( { field => 'field_name', cond => { value1 => { # hash to merge with when $input->{field_name} is == 'value1', }, }, on_true => { # hash to merge with when $input->{field_name} is true }, on_false => { # hash to merge with when $input->{field_name} is false }, # fields to merge with anyway where => { 'test' => '$field_name', }, tables => [ [ 'table', ] ], # NATURAL LEFT JOIN with table fields => [ \'(SELECT COUNT(*) FROM s WHERE s.id = this.id) AS cnt' ], }, { .... }, );
At first mechanism merges instance SQL::Abstract values with filters one. Then any field name given in ->{field} will be checked against conditions given in cond, if none is found here, on_true or on_false will be used.
->{field}
cond
on_true
on_false
When condition hash is fetched (either from cond, on_true or on_false) code recurses into that hash, which can contain cond, field and on_true/on_false values as well and should contain at least where field.
field
where
After all filters are processed, $self-_set_input> is called and using Data::Visitor all filter values started with $ are replaced with corresponding $input value. You can subclass and replace either _set_input or visit_hash/visit_value to substitute values your own way (for example, substitute array).
$self-
$
$input
_set_input
visit_hash
visit_value
Please, see tests for more details. More documentation is pending.
One main function is the constructor, which gets almost all data need to build a filter. Another function is "_make_filter" which merges all the data to make the filter's SQL::Abstract data. And final one is "select" which converts SQL::Abstract to statement and bind values one can supply to ->prepare and ->execute.
The new() function takes following options
new()
Table name to generate SQL query for. Can be list of tables (arrayref).
Field (or fields) name to fetch from table. Can be array of fields or just single field.
Filter - arrayref of described above format.
Input data for constructing filter SQL based on these data.
Processes input and filter and then adds SQL::Abstract data to $self using values from $self->{input} and filter from $filter. If $filter is not given, then $self->{filter} is used, if even this dont helps, $self->get_filter is called.
$self
$self->{input}
$filter
$self->{filter}
$self->get_filter
This function recurses heavily. Logic is described above.
Merge $fields into $self. Merged fields (list of fields to select), tables (list of tables to select from, including join), where (hash with conditions in form of SQL::Abstract).
$fields
fields
tables
$self->_set_input()
Substitutes $self->{input} values into $self->{where} hash, using magic of Data::Visitor. You can extend this method with arbitrary one to, for example, substitute array values.
$self->{where}
$self->visit_value()
$self->visit_hash_value()
Methods for Data::Visitor. First one replaces '$value' by $input->{value}, second one searches for -like key and changes it values in appropriate way. In exactly, processes LIKElity patterns.
$input->{value}
-like
$self->select()
Returns SELECT statement and bind values from call of SQL::Abstract.
SQL::Abstract, DBIx::Class, Data::Visitor.
Copyright (c) 2009 Pavel Boldin <davinchi@cpan.org>. All Rights Reserved.
This module is free software; you may copy this under the terms of the GNU General Public License, or the Artistic License, copies of which should have accompanied your Perl kit.
1 POD Error
The following errors were encountered while parsing the POD:
You forgot a '=back' before '=head2'
To install SQL::Filter, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::Filter
CPAN shell
perl -MCPAN -e shell install SQL::Filter
For more information on module installation, please visit the detailed CPAN module installation guide.