NAME

SQL::Filter - Generate complex SQL where from Perl data structures

SYNOPSIS

    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;

DESCRIPTION

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.

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.

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.

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

Please, see tests for more details. More documentation is pending.

FUNCTIONS

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.

new(option => 'value')

The new() function takes following options

table

Table name to generate SQL query for. Can be list of tables (arrayref).

field

Field (or fields) name to fetch from table. Can be array of fields or just single field.

filter

Filter - arrayref of described above format.

input

Input data for constructing filter SQL based on these data.

$self->_make_filter( [ $filter ] )

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.

This function recurses heavily. Logic is described above.

$self->_merge( $fields )

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

$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->visit_value() =head2 $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.

$self->select()

Returns SELECT statement and bind values from call of SQL::Abstract.

SEE ALSO

SQL::Abstract, DBIx::Class, Data::Visitor.

AUTHOR

Copyright (c) 2009 Pavel Boldin <davinchi@cpan.org>. All Rights Reserved.

LICENSE

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.

cpanm SQL::Filter

perl -MCPAN -e shell
install SQL::Filter