Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

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

by Your Mother (Canon)
on Sep 17, 2013 at 03:01 UTC ( #1054375=note: print w/ replies, xml ) Need Help??


in reply to Querying data with and & or when using DBIx::Class

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


Comment on Re: Querying data with and & or when using DBIx::Class
Select or Download Code
Re^2: Querying data with and & or when using DBIx::Class
by phildeman (Acolyte) on Sep 17, 2013 at 13:09 UTC

    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.

        Thanks again for your response.

        I am using MooseX.

        The issue is resolved. Instead of building the WHERE clause in $whrcls, I simply added the complete DBIx::Class search within the if/else statement:

        method get_colors( $id, $color1, $color2 ){
            my @mycolor_objs;
            if(!$color2){
                @mycolor_objs = $schema->resultset( 'TblColors' )->search({ ID => $id, color => $color1 },
                                        { order_by => 'ID' });
            }
            else {
                @myscolor_objs = $schema->resultset( 'TblColors' )->search({ ID => $id,
                                        -or => [ color => $color1, color => $color2 ] },
                                        { order_by => 'ID' });
            }
        }

        I am unsure at this moment, but it seems DBIx::Class is a little sensitive when using a scalar to represent the hash being passed as the WHERE clause. Thanks again. When I have a little more time, I will research building WHERE clauses with scalars.
        Thanks again for your response.

        I am using MooseX.

        The issue is resolved. Instead of building the WHERE clause in $whrcls, I simply added the complete DBIx::Class search within the if/else statement:

        method get_colors( $id, $color1, $color2 ){
            my @mycolor_objs;
            if(!$color2){
                @mycolor_objs = $schema->resultset( 'TblColors' )->search({ ID => $id, color => $color1 },
                                        { order_by => 'ID' });
            }
            else {
                @myscolor_objs = $schema->resultset( 'TblColors' )->search({ ID => $id,
                                        -or => [ color => $color1, color => $color2 ] },
                                        { order_by => 'ID' });
            }
        }

        I am unsure at this moment, but it seems DBIx::Class is a little sensitive when using a scalar to represent the hash being passed as the WHERE clause.

        Thanks again. When I have a little more time, I will research building WHERE clauses with scalars.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (7)
As of 2014-08-23 19:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (178 votes), past polls