[Dbix-class] RE: Best practice for case-insensitive searches

[Alexander Hartmaier]

On 2013-04-24 19:40, stephenmoy wrote:

Sorry Alex, I left out the '=3D>' when I copied the query.

I tried the examples in the SQL::Abstract docs as well, but still in each
case, any multiple 'LOWER' functions gave an error.

I.E.:  these work fine:
A) my @students =3D $schema->resultset( 'GraduateStudent' )->search(
                            -or =3D> [ 'LOWER( me.email )' =3D> $srch,
                                        'me.uid'                 =3D> $srch,
                            ]
);

B) my @students  =3D $schema->resultset( 'GraduateStudent' )->search({ -or =
=3D>
[
                                \[ 'LOWER(email) =3D ?', [ plain_value =3D>
$srch ] ],
                                uid =3D> $srch,
                          ]});


these don't work:
C) my @students =3D $schema->resultset( 'GraduateStudent' )->search(
                            -or =3D> [ 'LOWER( me.email )' =3D> $srch,
                                        'LOWER( me.uid )'    =3D> $srch,
                            ]
);

D) my @students  =3D $schema->resultset( 'GraduateStudent' )->search({ -or =
=3D>
[
                                \[ 'LOWER(email) =3D ?', [ plain_value =3D>
$srch ] ],
                                \[ 'LOWER(uid) =3D ?', [ plain_value =3D> $=
srch
] ],
                          ]});

I wonder if this is a DBIC issue or an Ingres ODBC issue.


Best,
Stephen

Looks like you need to manually define the bind datatype, see [1]:

my @students  =3D $schema->resultset( 'GraduateStudent' )->search({
    -or =3D> [
        # the long form
        \[ 'LOWER(email) =3D ?', [ { sqlt_datatype =3D> 'string' }, $srch ]=
 ],
        # the shortcut
        \[ 'LOWER(uid) =3D ?', [ \'integer'  =3D> $srch ] ],
    ]});

Note that the upcoming DBIx::Class version has both simplified syntax for b=
ind values as well as (hopefully) better docs [2].


[1] https://metacpan.org/module/DBIx::Class::ResultSet#DBIC-BIND-VALUES
[2] https://metacpan.org/module/RIBASUSHI/DBIx-Class-0.08249_04/lib/DBIx/Cl=
ass/ResultSet.pm#DBIC-BIND-VALUES






--
View this message in context: http://dbix-class.35028.n2.nabble.com/Best-pr=
actice-for-case-insensitive-searches-tp5963279p7578139.html
Sent from the DBIx-Class mailing list archive at Nabble.com.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co=
.uk




*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
"*
T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
"*
Notice: This e-mail contains information that is confidential and may be pr=
ivileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
"*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130425/bb4=
1c146/attachment.htm