DBIx::Class and SQL::Abstract

So, I've been working on this database driven website for a while and I'm rather surprised it took me so long to trip over this problem. How do you write an Oracle date range query with DBIx:Class? (The answer is not, as some might have it "stop using oracle".)

The sql is simple enough, but it has the inherent challenge for an ORM of needing to pass functions into the where clause.

select * from mytable where mydate between to_date('20090922 00:00:00', 'YYYYMMYY HH24:MI:SS') and to_date('20090922 23:59:59', 'YYYYMMYY HH24:MI:SS')

The DBIx:Class Cookbook talks a bit about calling functions from a select clause, but doesn't specifically address the where clause as different from the select clause. And between is sort of a funny case anyhow. A look at SQL::Abstract on which DBIx::Class's where clauses are based yielded examples which *help*. You're supposed to be able to use a reference to an array ref to do something like this:

fieldname =>{$operator, \[$function_snippet => @values_to_plug_in]}

But my code attempting multiple variations on this with the between keyword was not working.
So I sent email to the DBIx::Class folks, and sure enough. Looks like there was a bug in SQL::Abstract. Which got swatted almost instantly. Go Peter Rabbitson.

The following code is a working example of a date range query where start and end are dates passed in.

my $rs = $schema->resultset('User_Read_Log')->search(
{
datetime => {
"between",
[
\[ "to_date(?, 'YYYYMMDD HH24:MI:SS')", "$start 00:00:00" ],
\[ "to_date(?, 'YYYYMMDD HH24:MI:SS')", "$end 00:00:00" ]
]
}

This is current and working as of SQL::Abstract to 1.60.

Catalyst and DBIX::Class tip

I hadn't been using DBIX::Class with Catalyst very long before I realized that stuffing an array of Row objects into a session was a Bad Idea. a. You probably don't need all that schema and database state info that comes with it just to load your data into your html. b. you probably don't really want to try to stuff a live connection to a database into your session object and c. the things take up a boat load of session memory.

Although the DBIX::Class docs are actually pretty good it took me a while to come up with the answer:

DBIx::Class::ResultClass::HashRefInflator. If all you need is your data stuffed into a hashref and you're not using prefetches, this is what you want to use to keep your results light weight.

Sample code below is simple and works like a charm:

use DBIx::Class::ResultClass::HashRefInflator;

my $rs = $schema->resultset('CD');
$rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
while (my $hashref = $rs->next) {
...
}