DBIx::Class case insensitve substring search.

chrestomanci has asked for the wisdom of the Perl Monks concerning the following question:

I am looking for a simple way to search in a database for all rows where a particular column has a case insensitive sub-string match to a search string.

Google told me to use the LOWER() SQL function, so I wrote some literal SQL like this:

SELECT * FROM tblItem me where( lower(me.name) LIKE '%example%' )

It works nicey, however I am having trouble finding a neat way to translate it into a DBIx::Class::ResultSet search term.

Google searches found some posts on the DBIx-Class mailing list that said that I should write:

my $searched_rs = $all_items->search({'LOWER(me.name)' => {'LIKE'=>'%example%'})

However this did not work. I got an error: DBI Exception: DBD::mysql::st execute failed: Unknown column 'LOWER(me.name)'

From reading the FAQ, it said that the only way to use functions on the left side of a search term is to pass the whole query as literal SQL. That gives me:

my $searched_rs = $all_items->search( \[ 'LOWER(me.name) LIKE ?',[ plain_value => "%example%"] ] );

That works, but it is ugly, does not look to be very portable, feels against the spirit of using SQL::Abstract to insulate the perl program from the different database dialects of SQL.

Is there a neater way to do case insensitive sub-string searches? I am using MySQL and SQLite BTW.

Comment on DBIx::Class case insensitve substring search. Select or Download Code

Replies are listed 'Best First'.
Re: DBIx::Class case insensitve substring search. by duelafn (Parson) on May 25, 2011 at 20:26 UTC
Your result from google searching works for me. Try again? (code as you have it is a syntax error!) `my $searched_rs = $all_items->search({ 'LOWER(me.name)' => { 'LIKE' => '%example%' } });` You can also trace the DBIx::Class queries using `DBIC_TRACE` `DBIC_TRACE=1 perl /tmp/test.pl` Good Day, Dean	[reply] [d/l] [select]
Re: DBIx::Class case insensitve substring search. by bart (Canon) on May 26, 2011 at 11:18 UTC
I haven't used DBIx::Class in years, but... Unknown column 'LOWER(me.name)' This error message suggests to me that your field names get quoted, so it's possible to use column names with spaces and/or reserved words, which is a good thing, but which prevents hacks like these. I am guessing that there is a setting you set in DBIx::Class that enables this column name quoting. If you find out what setting, you could (temporarily) disable it. update A little bit of searching with Google and I have found something: quote_names quote_names When true automatically sets "quote_char" and "name_sep" to the characters appropriate for your particular RDBMS. This option is preferred over specifying "quote_char" directly. Some forum questions suggest that if quote_char and name_sep are set, DBIx::Class will automatically use them.	[reply]
Re^2: DBIx::Class case insensitve substring search. by duelafn (Parson) on May 26, 2011 at 16:25 UTC
Good find. After poking around, it seems that indeed the only options available to the OP is globally disabling quote_names / quote_char (which may be impossible) or resorting to the literal SQL as the OP found in the FAQ (I'd love to be proved wrong though). The documentation (and experiments) show that locally disabling quote_names / quote_char is difficult to the point of being not possible. Of course, as a key value, the typical SQL::Abstract "trick" of using a scalar reference `\'LOWER(me.name)'` is useless as well. Good Day, Dean	[reply] [d/l]
Re: DBIx::Class case insensitve substring search. by Khen1950fx (Canon) on May 25, 2011 at 14:56 UTC
Here's an example of a lower-case search.	[reply]
Re^2: DBIx::Class case insensitve substring search. by chrestomanci (Priest) on May 25, 2011 at 19:08 UTC
Is it? It looks more like a regular expression search using PostgreSQL, which is not what I am looking for. In any case, it uses SQL::KeywordSearch which has nothing to do with DBIx::Class or SQL::Abstract.	[reply]