Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Querying data with and & or when using DBIx::Class

by phildeman (Acolyte)
on Sep 16, 2013 at 21:27 UTC ( #1054337=perlquestion: print w/ replies, xml ) Need Help??
phildeman has asked for the wisdom of the Perl Monks concerning the following question:

I am using DBIx::Class and Catalyst. I need to perform a search using and & or. So, the SQL statement looks like this:

SELECT *
WHERE ID=1234 and (color='Blue' or color='Orange')
Order By ID

Any thoughts or suggestions?

Thanks.

Comment on Querying data with and & or when using DBIx::Class
Re: Querying data with and & or when using DBIx::Class
by McA (Curate) on Sep 17, 2013 at 00:09 UTC
      Thanks for the links. I actually used a sample script from the DBIx::Class::Manual::Cookbook:

      -and => [ -or => [ color => 'Blue', color => 'Orange', ], ID => 1234 ]

      and I got the following error:

      DBIx::Class::ResultSet::search(): DBI Exception: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND => [ -OR => COLOR => BLUE, COLOR => ORANGE, , ID => 1234, ], ) )
Re: Querying data with and & or when using DBIx::Class
by Your Mother (Canon) on Sep 17, 2013 at 03:01 UTC
    my $rs = $c->model("YOUset::THISupSOMEWHERE") ->search({ id => 1234, color => [qw/ Blue Orange /] }); # Same but different query form for conceptual reuse- $c->model("YOUset::THISupSOMEWHERE") ->search({ id => 1234, -or => [ color => "Blue", color => "Orange" ] });

    DBIC in a Cat app is usually exposed as above. It sounds like you have not done any reading yet. DBIx::Class and Catalyst each have pretty big learning curves. You're gonna have to dig in and read and experiment if you want to really make use of their power. SQL::Abstract is the engine behind DBIC, so you can test it out without a DB or the schema classes–

    use SQL::Abstract; my $sql = SQL::Abstract->new; { my ( $stmt, @bind ) = $sql ->select("Table", "*", { id => 1234, color => [qw/ Blue Orange /] }); print $stmt, $/; } { my ( $stmt, @bind ) = $sql ->select("Table", "*", { id => 1234, -or => [ color => "Blue", color => "Orange" ] }); print $stmt, $/; } __END__ SELECT * FROM Table WHERE ( ( ( color = ? OR color = ? ) AND id = ? ) +) SELECT * FROM Table WHERE ( ( ( color = ? OR color = ? ) AND id = ? ) +)

      Thanks for the help. I have done reading, but what I have tried was giving me errors. I had something similar to your first to examples.

      method get_my_mixedskills( $id, $color1, $color2 ) {
         my $whrcls;
         if(!$color2){
             $whrcls = "ID => $id, color => $color1";
         }
         else {
             $whrcls = "-and => [
                     -or => [ color => $color1, color => $color2, ],
                     ID => $id, ], "
          }
          my @myskills_objs = $schema->resultset( 'TblSkillSelected' )->search({ $whrcls },
                         { order_by => 'SID' });
      }

      But using this I got an error, listed in the previous post.

      Yours script is slightly different. I will try that.

      Thanks again

        So, what's going on here is you're using a string in place of a(n anonymous) hash.

        $whrcls = "ID => $id, color => $color1";

        Should be-

        $whrcls = { ID => $id, color => $color1 }; # OR something like my %params = ( ID => $id, color => $color1 ); $whrcls = \%params;

        It looks like you might be using some global variables and non-standard code structures (method instead of plain sub). Globals are going to tend to come back and bite you. Definitely avoid them. Non-standard stuff like method signatures has a dearth of examples and help and can come with really deep and difficult edge case bugs. I don't want to put you off that necessarily but if you're having a little trouble with Perl syntax it might not be the best place to jump in.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1054337]
Approved by choroba
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (14)
As of 2014-10-24 13:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (132 votes), past polls