[Dbix-class] Using 'like' query with data containing wildcard characters requiring ESCAPE?

[Dagfinn Ilmari Mannsåker]

Kenneth Ölwing <kenneth at olwing.se> writes:

> After some looksee I figured out that I can do a naughty literal thing:
>
> ###
> ...
> my @someRows = $rs->search( { data => { like => \q('%\_%' ESCAPE '\') } });
> ...
> ###
>
> That does print the expected two rows, but it seems a bit messay to have
> to go to literal sql to get it done, especially since this should be
> generic code that gets the actual search value from user input and thus
> should work with bind data which apparently works with ESCAPE clause as
> intended:
>
>     SELECT * FROM test WHERE (data LIKE ? ESCAPE '\'); # and bind the
> data, works in sqlitestudio
>
> So even if a literal piece would be required, I don't at the moment see
> how I can do it so bind is used in the Perl code...?

You need to use an arrayrefref to combine literal SQL and bind
parameters:

    $rs->search({ data => { like => \[q{? ESCAPE '\'}, '%\_%'] } });

The SQL::Abstract documentation has more information:

https://metacpan.org/pod/SQL::Abstract#Literal-SQL-with-placeholders-and-bind-values-subqueries

And DBIx::Class::ResultSet documents DBIC-specific bind parameter
details:

https://metacpan.org/pod/DBIx::Class::ResultSet#DBIC-BIND-VALUES 

>
> ken1
>
> On 2014-07-21 13:22, Bob MacCallum
>  wrote:
>> Hi Ken,
>>
>> This works for me - did you try the obvious?
>>
>> $resultset->search({ name => { 'like' => '%\_%' } } )
>>
>> cheers,
>> Bob
>>
>>
>>
>>
>>
>> On Fri, Jul 18, 2014 at 7:24 PM, Kenneth Ölwing <kenneth at olwing.se
>> <mailto:kenneth at olwing.se>> wrote:
>>
>>     Hi,
>>
>>     I happen to have text data in my table that has the '_'
>>     (underscore) character, and I wanted to do a like search.
>>
>>     So to find all rows with '_' I did:
>>
>>         my @rows = $rs->search( { data => { like => '%_%' } } );
>>
>>     and running with DBIC_TRACE=1, I see:
>>
>>         SELECT me.data FROM test me WHERE ( data LIKE ? ): '%_%'
>>
>>     I didn't get the result I was expecting. And duh, it dawned on me
>>     that '_' is itself a wildcard char...
>>
>>     After realising that, I researched and found out that I could
>>     write something like this (a direct query):
>>
>>         SELECT * FROM test WHERE (data LIKE '%\_%' ESCAPE '\');
>>
>>     Being a beginner with DBIx::Class, I may not have found the right
>>     place to look, but so far no luck...can I achieve this effect with
>>     the Perl code, and if so how? I'm using SQLite for the moment and
>>     Perl 5.16 and latest DBIx::Class.
>>
>>     TIA, any help appreciated.
>>
>>     ken1
>>
>>
>>     -----
>>     No virus found in this message.
>>     Checked by AVG - www.avg.com <http://www.avg.com>
>>     Version: 2014.0.4716 / Virus Database: 3986/7876 - Release Date:
>>     07/18/14
>>
>>
>>     _______________________________________________
>>     List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>>     IRC: irc.perl.org#dbix-class <http://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
>>
>>
>>
>>
>> _______________________________________________
>> 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
>>
>>
>> No virus found in this message.
>> Checked by AVG - www.avg.com <http://www.avg.com>
>> Version: 2014.0.4716 / Virus Database: 3986/7891 - Release Date: 07/21/14
>>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2014.0.4716 / Virus Database: 3986/7894 - Release Date: 07/21/14

-- 
"I use RMS as a guide in the same way that a boat captain would use
 a lighthouse.  It's good to know where it is, but you generally
 don't want to find yourself in the same spot." - Tollef Fog Heen