[Dbix-class] Searching based on a Result Class Method

[Rob Kinyon]

2010/3/23 Hetényi Csaba <csabiwork at tata.hu>:
> Dear Members
>
> I have an other beginner question.
> I followed the Catalyst tutorial:
> http://search.cpan.org/~hkclark/Catalyst-Manual-5.8004/lib/Catalyst/Manual/Tutorial/04_BasicCRUD.pod
>
> which describe, how to add own "Result Class Methods" to a result class
> below the:
> # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum: ....
> line.
> It is works, this added data can be used in template to print, but i don't
> know how to search based on it.
>
> An example:
> I have a table, which holds user informations, "born" date among other
> things.
> I added a Result Class Method to get the user's age based on table column:
> "born"
>
> Here is a cut with relevant sections:
> .
> .
> .
>   "born",
>   {
>     data_type => "date",
>     default_value => "'2000-01-01'::date",
>     is_nullable => 1,
>     size => 4,
>   },
>
> # Created by DBIx::Class::Schema::Loader v0.04006 @ 2010-03-10 15:38:32
> # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:dZDZBfpShIvfiiRiqCVN1Q
>
> sub age {
>     my ($self) = @_;
>
>     my $dt = DateTime->now;
>     my $born = DateTime->new( year   => $self->born->year(),
>                         month  => $self->born->month(),
>                         day    => $self->born->day(),
>     );
>
>     my $dur = $dt->subtract_datetime($born);
>
>     return ($dur->years);
> }
> .
> .
> .
>
> If i would like to search by age, it not works, because it says:
>
> "DBI Exception: DBD::Pg::st execute failed: ERROR:  column "age" does not
> exist..."
>
> The search code in controller:
>
> 	my $search_clause = {
> 		age => 15,
>
> 		};
>
> 	my $rs = $c->model('DB::Users')->search(
> 		$search_clause,
> 	);

There is no simple way to do this. The short answer is because
searching reaches the database in a completely different manner than
the resultset methods you're talking about building.

There -are- DBIC-ish ways to do this, but they are probably more
complicated than you can work with.

The -simplest- way to do this is to create a age_search-clause()
method that looks something like:

sub age_search_clause {
    my ( $self, $age_in_years ) = @_;
    return (
        \[ "DATEDIFF( NOW(), born IN YEARS ) = ?", $age_in_years ]
    );
}

You'd use that as so:

$rs->search([
    $rs->age_search_clause( 15 ),
    { name => $name },
])

Rob