<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF">
<div class="moz-cite-prefix">On 2013-04-24 19:40, stephenmoy wrote:<br>
</div>
<blockquote type="cite">
<pre>Sorry Alex, I left out the '=>' 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 = $schema->resultset( 'GraduateStudent' )->search(
-or => [ 'LOWER( me.email )' => $srch,
'me.uid' => $srch,
]
);
B) my @students = $schema->resultset( 'GraduateStudent' )->search({ -or =>
[
\[ 'LOWER(email) = ?', [ plain_value =>
$srch ] ],
uid => $srch,
]});
these don't work:
C) my @students = $schema->resultset( 'GraduateStudent' )->search(
-or => [ 'LOWER( me.email )' => $srch,
'LOWER( me.uid )' => $srch,
]
);
D) my @students = $schema->resultset( 'GraduateStudent' )->search({ -or =>
[
\[ 'LOWER(email) = ?', [ plain_value =>
$srch ] ],
\[ 'LOWER(uid) = ?', [ plain_value => $srch
] ],
]});
I wonder if this is a DBIC issue or an Ingres ODBC issue.
Best,
Stephen</pre>
</blockquote>
Looks like you need to manually define the bind datatype, see [1]:<br>
<pre>my @students = $schema->resultset( 'GraduateStudent' )->search({
-or => [
# the long form
\[ 'LOWER(email) = ?', <code class="pl plain">[ { </code><code class="pl string">sqlt_datatype</code> <code class="pl plain">=> </code><code class="pl variable">'string'</code> <code class="pl plain">}, </code><code class="pl variable">$srch</code> <code class="pl plain">] </code>],
# the shortcut
\[ 'LOWER(uid) = ?', <code class="pl plain">[ \</code><code class="pl variable">'integer'</code> <code class="pl plain">=> </code><code class="pl variable">$srch</code> <code class="pl plain">] </code>],
]});</pre>
<br>
Note that the upcoming DBIx::Class version has both simplified syntax for bind values as well as (hopefully) better docs [2].<br>
<br>
<br>
[1] <a class="moz-txt-link-freetext" href="https://metacpan.org/module/DBIx::Class::ResultSet#DBIC-BIND-VALUES">
https://metacpan.org/module/DBIx::Class::ResultSet#DBIC-BIND-VALUES</a><br>
[2] <a class="moz-txt-link-freetext" href="https://metacpan.org/module/RIBASUSHI/DBIx-Class-0.08249_04/lib/DBIx/Class/ResultSet.pm#DBIC-BIND-VALUES">
https://metacpan.org/module/RIBASUSHI/DBIx-Class-0.08249_04/lib/DBIx/Class/ResultSet.pm#DBIC-BIND-VALUES</a><br>
<br>
<blockquote type="cite">
<pre>
--
View this message in context: <a class="moz-txt-link-freetext" href="http://dbix-class.35028.n2.nabble.com/Best-practice-for-case-insensitive-searches-tp5963279p7578139.html">http://dbix-class.35028.n2.nabble.com/Best-practice-for-case-insensitive-searches-tp5963279p7578139.html</a>
Sent from the DBIx-Class mailing list archive at Nabble.com.
_______________________________________________
List: <a class="moz-txt-link-freetext" href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</a>
IRC: irc.perl.org#dbix-class
SVN: <a class="moz-txt-link-freetext" href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a>
Searchable Archive: <a class="moz-txt-link-freetext" href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk">http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</a>
</pre>
</blockquote>
<br>
<br>
<font face="Verdana" color="Purple" size="2"><br>
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*<br>
T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien<br>
Handelsgericht Wien, FN 79340b<br>
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*<br>
Notice: This e-mail contains information that is confidential and may be privileged.<br>
If you are not the intended recipient, please notify the sender and then<br>
delete this e-mail immediately.<br>
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*<br>
</font>
</body>
</html>