[Dbix-class] SQL functions in WHERE clause

[Ivan Fomichev]

Hello, all!

IMHO, one of the biggest omissions in SQL::Abstract, and as a result
in DBIx::Class, is lack of possibility to use SQL functions in WHERE
clauses. Let's admit, we have a table

CREATE TABLE patients (
  id int(10) unsigned NOT NULL auto_increment,
  first_name varchar(64) NOT NULL default '',
  last_name varchar(64) NOT NULL default '',
  birth_date date default NULL,
  is_blocked tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY last_name (last_name,first_name),
  KEY birth_date (birth_date),
  KEY is_blocked (is_blocked)
) TYPE=MyISAM;

I would like to search for patients who are born 20 years ago and
whose first name begins with 'J'. Now it can be achieved only by the
following construct:

    my $rs = $schema->resultset('Patient')->search(
        {
            -and => [
                first_name => { like => 'J%' },
                \'`birth_date` < NOW() - INTERVAL 20 YEAR',
            ],
        },
    );

This is not quite convenient, 1) to use '-and', 2) to construct SQL by
oneself, which may result in missing SQL code injections and affect
security.

How much is it worth to implement SQL functions inside WHERE clause natively?

An appropriate syntax could be something like this (note scalarref):

    my $rs = $schema->resultset('Patient')->search(
        {
            first_name => { like => 'J%' },
            birth_date => { '<' => [ \'NOW() - INTERVAL ? YEAR' => 20 ] },
        },
    );

Regards,
Ivan