Extracting date parts using DBIC while keeping queries database agnostic

Question

I use a MySQL database in production and a SQLite database for running tests. One part of my application is used to gather monthly statistics for a year. I've successfully done this, however it came at a cost of not being able to automate tests because I'm using MySQL specific functions when querying for the data:

my $criteria = {
    status => ['Complete'],
    'YEAR(completed_on)' => DateTime->now()->year(),
};

my $attributes = {
    select => [ { count => 'title' }, 'completed_on' ],
    as => [qw/num_completed datetime/],
    group_by => [qw/MONTH(completed_on)/],
};

Notice I'm using YEAR and MONTH MySQL functions.

I know one way I can substitute the where clause to eliminate the use of MySQLs YEAR function, something like this:

my $dtf = $schema->storage->datetime_parser;
my $begin_date = DateTime->from_day_of_year( year => DateTime->now()->year(), day_of_year => 1 ); #inception o_O
my $end_date = DateTime->from_day_of_year( year => DateTime->now()->year(), day_of_year => 36[56] );

my $criteria = {
    status => ['Complete'],
    completed_on =>
    -between => [
        $dtf->format_datetime($begin_date),
        $dtf->format_datetime($end_date),
    ]
};

Using the recommended way to query date fields using DBIC

But I'm stumped as to what to do with the group_by clause and how to make the grouping of this fields date value by month database agnostic as well. Wondering if anyone has any ideas?

Thanks!

score 1 · Answer 1 · answered Mar 01 '16 at 09:43

Sometimes you will have to make engine specific code in DBIx::Class if you're trying to do special things. You can use $schema->storage->sqlt_type to make different SQL.

Note you can also use substr(completed_on,1,4) to get the year in SQLite.

This will solve your problem:

my $type = $schema->storage->sqlt_type;

my $criteria;
my $attributes;

if ($type eq 'MySQL') {
    $criteria = {
        status => ['Complete'],
        'YEAR(completed_on)' => DateTime->now()->year(),
    };

    $attributes = {
        select => [ { count => 'title' }, 'completed_on' ],
        as => [qw/num_completed datetime/],
        group_by => [qw/MONTH(completed_on)/],
    };
}
elsif ($type eq 'SQLite') {
    my $dtf = $schema->storage->datetime_parser;
    my $begin_date = DateTime->from_day_of_year( year => DateTime->now()->year(), day_of_year => 1 ); #inception o_O
    my $end_date = DateTime->from_day_of_year( year => DateTime->now()->year() + 1, day_of_year => 1 )->add( seconds => -1 );

    $criteria = {
        status => ['Complete'],
        completed_on => {
            -between => [
                $dtf->format_datetime($begin_date),
                $dtf->format_datetime($end_date),
            ]
        }
    };

    $attributes = {
        select => [ { count => 'title' }, 'completed_on' ],
        as => [qw/num_completed datetime/],
        group_by => ['substr(completed_on,6,2)'],
    };
}

Extracting date parts using DBIC while keeping queries database agnostic

1 Answers1