FAQ
Hi,



I'd like to do an case-insensitive search on one of my database tables
with DBIx::Class. As I haven't

found any documentation about case-insensitive search, I am wondering
what's the best practice

to do so.



What I would usually do with the regular DBI modul, is to create a
simple query like this:

SELECT t.id FROM table t WHERE LOWER( t.my_col ) = '<my search string>';



Any idea or thought-provoking impulse advise is much appreciated.





Thanks

Winni

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20110126/13221a3a/attachment.htm

Search Discussions

  • Eden Cardim at Jan 26, 2011 at 7:18 pm
    "Winfried" == Winfried Neessen writes:
    Winfried> Hi, I?d like to do an case-insensitive search on one of my
    Winfried> database tables with DBIx::Class. As I haven?t
    Winfried> found any documentation about case-insensitive search, I
    Winfried> am wondering what?s the best practice
    Winfried> to do so.

    Winfried> What I would usually do with the regular DBI modul, is to
    Winfried> create a simple query like this:

    Winfried> SELECT t.id FROM table t WHERE LOWER( t.my_col ) = ?<my search string>?;
    Winfried> Any idea or thought-provoking impulse advise is much appreciated.

    $rs->search({ 'LOWER(me.my_col)' => 'my search string' })

    --
    Eden Cardim
    Software Engineer
    Shadowcat Systems Ltd.
    http://www.shadowcat.co.uk
    http://blog.edencardim.com
  • Winfried Neessen at Jan 27, 2011 at 8:42 am
    Hi Eden,
    $rs->search({ 'LOWER(me.my_col)' => 'my search string' })
    That was too easy ;-) Thanks a lot!


    Winni
  • Stephenmoy at Apr 24, 2013 at 2:56 pm
    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.
  • Alexander Hartmaier at Apr 24, 2013 at 3:39 pm
    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.
    *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
  • Stephenmoy at Apr 24, 2013 at 5:40 pm
    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






    --
    View this message in context: http://dbix-class.35028.n2.nabble.com/Best-practice-for-case-insensitive-searches-tp5963279p7578139.html
    Sent from the DBIx-Class mailing list archive at Nabble.com.
  • Alexander Hartmaier at Apr 25, 2013 at 7:30 am
    On 2013-04-24 19:40, stephenmoy wrote:


    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


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


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


    Note that the upcoming DBIx::Class version has both simplified syntax for bind 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/Class/ResultSet.pm#DBIC-BIND-VALUES












    --
    View this message in context: http://dbix-class.35028.n2.nabble.com/Best-practice-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 privileged.
    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/bb41c146/attachment.htm
  • Stephenmoy at Apr 26, 2013 at 2:34 pm
    Thanks for your help, Alex, but it didn't work.


    I have a feeling it's related to the Ingres ODBC driver we're using. =(








    --
    View this message in context: http://dbix-class.35028.n2.nabble.com/Best-practice-for-case-insensitive-searches-tp5963279p7578142.html
    Sent from the DBIx-Class mailing list archive at Nabble.com.
  • Aaron Trevena at Apr 29, 2013 at 12:53 pm

    On 26 April 2013 15:34, stephenmoy wrote:
    with ParamValues: 1='van', 2='van', 3=undef
    [ .. ]
    I have a feeling it's related to the Ingres ODBC driver we're using. =(

    One of the bind parameters shown in that error message shows an undef
    value - is that breaking it?


    A


    --
    Aaron J Trevena, BSc Hons
    http://www.aarontrevena.co.uk
    LAMP System Integration, Development and Consulting

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbix-class @
categoriesperl, catalyst
postedJan 26, '11 at 4:25p
activeApr 29, '13 at 12:53p
posts9
users5
websitedbix-class.org
irc#dbix-class

People

Translate

site design / logo © 2021 Grokbase