Tuesday, September 22, 2009

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 => {
\[ "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.