CODEWITHSUNDEEP
postgresqlperldbix-class
Sven Eppler
Sven Eppler

Reputation: 1706

DBIx::Class / SQL::Abstract Apply SQL-Function to column in WHERE clause

I just fiddled around with a complex SQL query generated with DBIx::Class (which under the hood uses SQL::Abstract for WHERE clauses).

I'm using the BETWEEN operator to filter entries:

# SELECT title FROM tracks WHERE begin BETWEEN '2016-07-01' AND '2016-07-02'
$searchArgs->{'begin'} = { BETWEEN => ['2016-07-01', '2016-07-02' ] };

This will skip begin-dates actually ON the 2016-07-02. Probably because those begin-values are past midnight (00:00) of the 2016-07-02 and therefore not in the BETWEEN range (see example data on the end).

On the SQL-Side, this is easily fixed:

SELECT title FROM tracks WHERE DATE(begin) BETWEEN '2016-07-01' AND '2016-07-02'
# Note the DATE()-cast of begin

Is there a way to achieve this with SQL::Abstract syntax? I tried the column name as a scalar-ref ($searchArgs->{\"DATE(begin)"}) to get it passed by untouched, but it didn't work out.

Just using $searchArgs->{'DATE(begin)'} triggers an SQL-Error since there is no such column "DATE(begin)".

# Minimal example data
CREATE TABLE test ( title VARCHAR(255), begin timestamp );
INSERT INTO test (title, begin) VALUES ( 'Track 1', '2016-07-01T12:00:00'::timestamp), ( 'Track 2', '2016-07-02 12:00:00'::timestamp );
SELECT title FROM test WHERE begin BETWEEN '2016-07-01' AND '2016-07-02';
SELECT title FROM test WHERE DATE(begin) BETWEEN '2016-07-01' AND '2016-07-02';

Upvotes: 4

Views: 479

Answers (1)

Borodin
Borodin

Reputation: 126752

The issue seems to be that a date like '2016-07-02' is equivalent to '2016-07-02T00:00:00', and '2016-07-02T12:00:00' is greater than that. Calling DATE() just truncates the time value so that the extra twelve hours are discarded

I suggest that you abandon BETWEEN, and use the equivalent >= and <= operators. You actually want all records up to but not including '2016-07-03T0:00:00' (that is, everything up to and including 2016-07-02T23:59:59 and any fractions of a second that your database may support) so you should write

SELECT title FROM test
WHERE begin BETWEEN >= '2016-07-01' AND begin < '2016-07-03';

or, in DBIx::Class

$searchArgs->{begin} = {
    '>=' => '2016-07-01',
    '<'  => '2016-07-03',
};

or if you have access only to the end date and want to avoid date arithmetic in your Perl code then just let the database do it for you

$searchArgs->{begin} = {
    '>=' => '2016-07-01',
    '<'  => \[ 'date(?) + 1', '2016-07-02' ],
};


If you insist on using a function instead of a simple table name in your WHERE comparison then it is possible by using literal SQL, but it's inadvisable as you will bypass all optimisation that the database can provide and it will have to resort to a linear search

The DBIx::Class::Manual::Cookbook documentation has this under Using SQL functions on the left hand side of a comparison

Using SQL functions on the left hand side of a comparison is generally not a good idea since it requires a scan of the entire table. (Unless your RDBMS supports indexes on expressions - including return values of functions - and you create an index on the return value of the function in question.) However, it can be accomplished with DBIx::Class when necessary by resorting to literal SQL

Upvotes: 5

Related Questions