Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Problems Using Join in DBIx::Class

by phildeman (Beadle)
on Dec 27, 2017 at 18:12 UTC ( #1206286=perlquestion: print w/replies, xml ) Need Help??
phildeman has asked for the wisdom of the Perl Monks concerning the following question:

Help!

I am attempting to use join in DBIx::Class. Perl will read the search component, then ignore the join component.

In the Result packages the code reads as follows:

TblPerson:
__PACKAGE__->add_columns( "PID", { data_type => "integer", extra => { unsigned => 1 }, is_auto_increment => 1, is_nullable => 0, }, "FName", { data_type => "varchar", is_nullable => 0, size => 30 }, "MI", { data_type => "char", is_nullable => 1, size => 2 }, "LName", { data_type => "varchar", is_nullable => 0, size => 30 }, "username", { data_type => "varchar", is_nullable => 0, size => 8 }, ); . . . __PACKAGE__->has_many( "tbl_requests", "DB::BSMS::Result::TblRequests", { "foreign.PID" => "self.PID" }, { cascade_copy => 0, cascade_delete => 0 }, );

TblRequests:
__PACKAGE__->add_columns( "RID", { data_type => "integer", extra => { unsigned => 1 }, is_auto_increment => 1, is_nullable => 0, }, "PID", { data_type => "integer", extra => { unsigned => 1 }, is_foreign_key => 1, is_nullable => 0, }, "original_post_date", { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 0 }, "last_update", { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 0 }, "submit_date", { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 }, "status", { data_type => "varchar", is_nullable => 0, size => 20 }, "sent_email", { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 }, ); . . . __PACKAGE__->belongs_to( "p", "DB::BSMS::Result::TblPerson", { PID => "PID" }, { is_deferrable => 1, on_delete => "NO ACTION", on_update => "NO ACT +ION" }, );

CALLING METHOD:
method get_user_request_status() { my @proc_requests = $schema->resultset( 'TblRequests' )->search({ -or => [ -and => [ -or => [ 'sent_email' => {'!=', undef }, 'sent_email' => {'!=', '0000-00-00'}, ], ], 'status' => 'Offered', 'status' => 'Denied', 'status' => 'Cancelled' 'status' => 'Conditional Offer', ], }, { join => 'p' } ); return \@proc_requests if @proc_requests; } }

When I exclude the the join component, the search component works fine. However, the
moment I introduce the join, the result set only returns data from TblRequests, and nothing from
TblPerson.

I ran debug, and it only displays the $schema statement upto the closing outter -or bracket "],". When
I press 'n' to go to the next line, it bypasses join component and goes right to the return statement. It simply
ignores the join component.

Any thoughts or suggestions? Thanks.

Replies are listed 'Best First'.
Re: Problems Using Join in DBIx::Class
by 1nickt (Abbot) on Dec 28, 2017 at 02:20 UTC

    Hi, since you are not apparently constraining your resultset on any columns from the joined table, I surmise that you are simply wanting to load all the related data from it in the same query. If so, according to FETCHING RELATED DATA in the DBIx::Class manual, don't you need prefetch? ("Of the three, only the prefetch technique will deal sanely with fetching related objects over a has_many relation.")

    In general, for debugging you might want to set DBIC_TRACE=1 in your env, so as to see the actual SQL being produced by your code. Also, try running a simpler query without the ORs, just for debugging clarity.

    Hope this helps!


    The way forward always starts with a minimal test.

      Thanks for your help. However, I tried using 'prefetch', and I get the same results as if I were using 'join'.

      I get the following error:

      Can't locate object method "LName" via package "myDBPackage::Result::T +blRequests" at test_processed_requests.pl line 15, <DATA> line 751.

      As you can see in my previous post, TblPerson does contain the column, 'LName'. That is the column name in the database, as well.

      I made a slight change to the -or and -and to produce the correct where clause statement. In addition, I included the following snippet of code, to the Calling Method:

      $schema->storage->debug(1);

      to produce the SQL Statement, below:

      SELECT me.RID, me.PID, me.original_post_date, me.last_update, me.submit_date, me.status, me.sent_email, p.PID, p.FName, p.MI, p.LName, FROM tblRequests me JOIN tblPerson p ON p.PID = me.PID WHERE ( sent_email IS NOT NULL OR sent_email != '0000-00-00' ) AND ( status = ‘Offered’ OR status = ‘Denied’ OR status = ‘Cancelled’ OR status = ‘Conditional Offer’ )

      So, the DBIx::Class call

      my @proc_requests = $schema->resultset( 'TblRequests' )->search({ -or => [ status => 'Offered', status => 'Denied', status => 'Cancelled', status => 'Conditional Offer', ], -and => [ -or => [ sent_email => {'!=', undef }, sent_email => {'!=', '0000-00-00'}, ], ], }, { prefetch => 'p' } );

      is correct. It produces the correct SQL Statement (above).
      Yet, it can't find object method from the other table. Again, I get the same error whether
      I use 'join' or 'prefetch'.

      And when I run debug, it displays the where clause of the DBIx::Class call, but it bypasses
      'prefetch' or 'join'.

        I never used DBIx::Class, but I see two flaws in your search statement:
        my @proc_requests = $schema->resultset( 'TblRequests' )->search({ -or => [ status => 'Offered', status => 'Denied', status => 'Cancelled', status => 'Conditional Offer', ], -and => [ -or => [ sent_email => {'!=', undef }, sent_email => {'!=', '0000-00-00'}, ], ], }, { prefetch => 'p' } );
        I sent this through perltidy, which renders
        my @proc_requests = $schema->resultset('TblRequests')->search( { -or => [ status => 'Offered', status => 'Denied', status => 'Cancelled', status => 'Conditional Offer', ], -and => [ -or => [ sent_email => { '!=', undef }, sent_email => { '!=', '0000-00-00' }, ], ], }, { prefetch => 'p' } );
        1. "and" is on the same level as the preceding "or". I doubt this will be translated to SQL the way you intend.
        2. The last "or" will be true for all records. You say "defined or not zero", while you probably meant "neither undefined nor zero"
        What you may have intended, might be
        my @proc_requests = $schema->resultset('TblRequests')->search( { -or => [ -or => [ status => 'Offered', status => 'Denied', status => 'Cancelled', status => 'Conditional Offer', ], -and => [ sent_email => { '!=', undef }, sent_email => { '!=', '0000-00-00' }, ], ], }, { prefetch => 'p' } );
        (at least this looks more "logical" to me)
        Can't locate object method "LName"

        Try using ->p->LName

        for (@proc_requests){ print join "\t",$_->PID,$_->p->LName,$_->sent_email,"\n"; };

        I agree with soonix, your SQL logic is odd. It will retrieve sent_email values of '0000-00-00' because they are defined.

        poj

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (5)
As of 2019-02-21 01:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    I use postfix dereferencing ...









    Results (108 votes). Check out past polls.

    Notices?
    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!