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/[EMAIL PROTECTED]/
_______________________________________________
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/[EMAIL PROTECTED]/
