[Dbix-class] SQL functions in WHERE clause

[Lee Standen]

Or, you could take the other tact, and argue that it should be something 
which is handled on the Perl side...

my $rs = $schema->resultset('Patient')->search({
	-and => [
		first_name => { like => 'J%' },
		birth_date => { '>' => time - 20 * SECONDS_IN_YEAR }
		]
	});

Yes, while it's POSSIBLE to do that in an SQL statement, I prefer to 
think as DBIx::Class not as an SQL abstractor, but as a data object 
extractor, which means anything that requires me to use SQL specific 
commands is bad :)

I would ideally like to see DBIx::Class eventually have backend modules 
for things like LDAP, CSV and other non-SQL storage types :)



Ivan Fomichev wrote:
> 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
> 
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: 
> http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>