http://www.perlmonks.org?node_id=1054337

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

Replies are listed 'Best First'.
Re: Querying data with and & or when using DBIx::Class
by Your Mother (Archbishop) 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" ] });

    [download]

    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 = ? ) 
+)

    [download]
[reply]
[d/l]
[select]

      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
[reply]

        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;

        [download]

        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.

[reply]
[d/l]
[select]
Re: Querying data with and & or when using DBIx::Class
by McA (Priest) on Sep 17, 2013 at 00:09 UTC
[reply]
      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, ], ) )
[reply]

Back to Seekers of Perl Wisdom