[Dbix-class] SQL functions in WHERE clause

Oleg Pronin syber.rus at gmail.com
Sat Apr 14 00:26:55 GMT 2007


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 <ifomichev at gmail.com>:
>
> 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=3DMyISAM;
>
> 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 =3D $schema->resultset('Patient')->search(
>        {
>            -and =3D> [
>                first_name =3D> { like =3D> '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 =3D $schema->resultset('Patient')->search(
>        {
>            first_name =3D> { like =3D> 'J%' },
>            birth_date =3D> { '<' =3D> [ \'NOW() - INTERVAL ? YEAR' =3D> 2=
0 ] },
>        },
>    );
>
> 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/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070414/55c=
b7286/attachment.htm


More information about the Dbix-class mailing list