[Dbix-class] Advent article on 'advanced search'

[Zbigniew Lukasiak]

Dear all,

I've submitted a proposal to write an article for the Catalyst Advent
Calendar about implementing an 'advanced search' (in a web app) - it
looks like it was approved, but before starting I would like to ask
you if my approach is sane.

Basically usually 'advanced search' lets you combine number of simple
predicates with an 'AND'.  When all the predicates are just tests on
some column in one table it is quite simple you just feed the hash of
the params you get from the web form and feed it directly into the
DBIC search like:

$schema->ResultSet( 'SomeTable' )->search( \%params, {} )

But then you need to add some tests on columns in a related table -
and you need to scan the %params hash and check which parameter is
from which table, and then do the join etc.  And then you need some
more complex predicates - like full text search, or searching by
proximity or you need to search for rows that are tagged by a set of
tags - and it starts to be complicated.  What I propose here is a
ResultSet base class that works for the simple case and for the
related tables and that is easily extendable, plus three extensions
for: full text search (based on PostgreSQL tsearch2), searching by
proximity (using again PostgreSQL features - unfortunately it uses a
planar geometry which is only partially useful on the spherical earth)
and for searching by a set of tags.

Here is the main function (directly from our dev environment):

sub advanced_search {
    my ( $self, $params ) = @_;
    my $rs = $self; #->search( );
    if( $params->{tags} and my $subref = $self->can( 'search_for_' .
'tags' ) ){
        # this is a hack around DBIC bug (fixed in the svn trunk)
            $rs = &$subref( $self, $rs, $params );
    }
    for my $column ( keys %$params ){
        next if $column eq 'tags';
        if( my $subref = $self->can( 'search_for_' . $column ) ){
            $rs = &$subref( $self, $rs, $params );
            next;
        }
        if ( $self->result_source->has_column( $column ) ){
            $rs = $rs->search( { $column => $params->{$column} } );
            next;
        }
        if ( $column =~ /(.*)\.(.*)/ ){
            my $relation = $1;
            $rs = $rs->search(
                { $column => $params->{$column} },
                { join => [ $relation ] }
            );
        }
    }
#    warn 'in advanced_serach: ' . Dumper( $rs->{attrs} ); use Data::Dumper;
    return $rs;
}

It works similarly to the standard 'search' methodin the ResultSet
class. It expects a hashref like:

{
    some_param => 'some value',
    other_param => 'other value',
    relation.param => 'value',
    tags => [ 'tag1', 'tag2', 'tag3' ]
}

For the 'relation.param' it creates a join.  For the more complicated
searches, like with the 'tags' parameter, you need to extend the
ResultSet with a 'search_for_*' method, 'search_for_tags' in the case
of tags.  So to add new complex predicates you need to only write this
new 'search_for_*' method - and you don't need to think about fitting
it into the whole picture.  In the attached full base class you can
see examples of that.

What do you think about that approach?  Is it sane? Is it useful? Do
you think it would be worth an Advent Calendar entry?  I am also
interested if there will be a DBIC release soon - so that I could
delete the code forcing search_for_tags to be called before all other
joins (it is fixed in:
http://dev.catalystframework.org/svnweb/bast/revision/?rev=3844).

Cheers,
Zbyszek
http://perlalchemy.blogspot.com/