Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

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

by Your Mother (Chancellor)
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: (6)
As of 2015-07-06 03:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (69 votes), past polls