This can be solved with operator overloading. There is a very good idea in
module 'ORM' (another DB abstraction module). Please see its docs (
http://search.cpan.org/~akimov/ORM-0.85/lib/ORM.pod) for information.
Thus this module is less powerfull then DBIx-Class, there are some cool
things. I think this would be great to merge this ideas with DBIx-Class.
2007/4/12, Ivan Fomichev <[EMAIL PROTECTED]>:
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]/
