DBIx::Class::ResultSet problems

Question

I've got the following code:

package MyPackage::ResultSet::Case;
use base 'DBIx::Class::ResultSet';

sub cases_last_fourteen_days {
    my ($self, $username) = @_; 

    return $self->search({
                username    => $username,
                date        => { '>=' => 'DATE_SUB(CURDATE(),INTERVAL 14 DAY)' },
    }); 
};

But when I try to use it this way:

$schema->resultset('Case')->cases_last_fourteen_days($username)

I always get zero results, can anyone tell what I'm doing wrong?

Thanks!

Answer 1

The way you use the SQL::Abstract condition would result in this where condition:

WHERE username = ? AND date >= 'DATE_SUB(CURDATE(),INTERVAL 14 DAY)'

When you wish to use database functions in a where clause you need to use a reference to a scalar, like this:

date        => { '>=' => \'DATE_SUB(CURDATE(),INTERVAL 14 DAY)' },

ProTip: if you set the environment variable DBIC_TRACE to 1, DBIx::Class will print the queries it generates to STDERR ... this way you can check if it really does what you wish.