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

[Alexander Hartmaier]

That's because your syntax is wrong:
-or { needs to be -or => [] as per SQL::Abstract docs.

and the LHS function too [1].

[1]
https://metacpan.org/module/RIBASUSHI/DBIx-Class-0.08210/lib/DBIx/Class/Manual/Cookbook.pod#Using-SQL-functions-on-the-left-hand-side-of-a-comparison

Best regards, Alex

On 2013-04-24 16:56, stephenmoy wrote:
> Has anybody tried searching using LOWER on more than one column?
>
> Such as:
>
> my $srch = "van";
> my @students  = $schema->resultset( 'GraduateStudent' )->search(
>                               -or {  'LOWER( me.email )' => $srch,
>                                       'LOWER( me.uid )'    => $srch,
>                                       'LOWER( me.st_nm )' => { like => '%' .
> $srch . '%' },
>                                    },
>                               { order_by => 'me.st_nm' }
> );
>
> gives an error:
>
> DBIx::Class::ResultSet::search(): DBI Exception: DBD::ODBC::st bind_param
> failed: [unixODBC][Ingres][Ingres ODBC Driver]SQL data type out of range
> (SQL-HY004) [for Statement "SELECT me.ssn, me.email, me.st_nm,
> me.c_xp_grd_dt, me.pn_f_rg, me.c_mj, me.aa_fl_p, me.ps_phone, me.c_dg,
> me.nyu_sc, me.pn_mj FROM rr01st me WHERE ( ( LOWER( me.email ) = ? OR LOWER(
> me.ssn ) = ? OR LOWER( me.st_nm ) LIKE ? ) ) ORDER BY me.st_nm" with
> ParamValues: 1='van', 2='van', 3=undef]
>
> Thanks in advance,
> Stephen
>
>
>
>
> --
> View this message in context: http://dbix-class.35028.n2.nabble.com/Best-practice-for-case-insensitive-searches-tp5963279p7578137.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 privileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*