[Dbix-class] Writing a query with a BETWEEN in the WHERE clause and the to_date(function)

[Will Hawes]

2009/9/21 Karen Hoofnagle <karen at hoofnagle.org>:
> Are you suggesting the code should look like this:
>
>   $rs = $c->model('DB::User_Read_Log')->search(
>        {
>         datetime =>{"between", [\"to_date(?, 'YYYYMMDD HH24:MM:SS')"
> =>$start." 00:00:00", \"to_date(?, 'YYYYMMDD HH24:MM:SS')" => $end. "
> 00:00:00"] }
>        }
> );
>
> I still get the same error (pasted again below).
>
> ERROR: DBIx::Class::ResultSet::all(): DBIx::Class::ResultSet::all():
> [SQL::Abstract::_where_field_BETWEEN] Fatal: special op 'between' requires
> an arrayref of two values (or a scalarref or arrayrefref for literal SQL) at
> /home/khoofnag/svn/RCN-TEADWeb/script/../lib/RCN/TEADWeb/Controller/WorkOrder.pm
> line 93

Ah - I had expected the following to work, but it doesn't:

my $where = {
    datetime => {
        "between",
        [
            \[ "to_date(?, 'YYYYMMDD HH24:MM:SS')", "$start 00:00:00" ],
            \[ "to_date(?, 'YYYYMMDD HH24:MM:SS')", "$end 00:00:00" ]
        ]
    }
};

It seems that while a reference to an arrayref can be used with
"between", elements within said arrayref are currently limited to
either scalars or references to scalars. Maybe a candidate for a
patch?

I think the following does what you want though:

my $where = {
    datetime => {
        "between",
        \[
            "to_date(?, 'YYYYMMDD HH24:MM:SS') AND to_date(?,
'YYYYMMDD HH24:MM:SS')",
            "$start 00:00:00",
            "$end 00:00:00"
        ]
    }
};