Problems Using Join in DBIx::Class

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 },
);
[download]

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" },
);
[download]

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;
  }
}
[download]

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.

Comment on Problems Using Join in DBIx::Class Select or Download Code

Replies are listed 'Best First'.
Re: Problems Using Join in DBIx::Class by 1nickt (Canon) 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.	[reply] [d/l] [select]
Re^2: Problems Using Join in DBIx::Class by phildeman (Scribe) on Dec 28, 2017 at 06:31 UTC
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.` [download] 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);` [download] 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’ )` [download] 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' } );` [download] 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'.	[reply] [d/l] [select]
Re^3: Problems Using Join in DBIx::Class by soonix (Chancellor) on Dec 28, 2017 at 08:48 UTC
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' } );` [download] 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' } );` [download] "and" is on the same level as the preceding "or". I doubt this will be translated to SQL the way you intend. 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' } );` [download] (at least this looks more "logical" to me)	[reply] [d/l] [select]
Re^4: Problems Using Join in DBIx::Class by phildeman (Scribe) on Dec 29, 2017 at 03:11 UTC
Re^5: Problems Using Join in DBIx::Class by poj (Abbot) on Dec 29, 2017 at 08:50 UTC
Re^5: Problems Using Join in DBIx::Class by 1nickt (Canon) on Dec 29, 2017 at 04:26 UTC
Re^3: Problems Using Join in DBIx::Class by poj (Abbot) on Dec 28, 2017 at 19:04 UTC
Can't locate object method "LName" Try using `->p->LName` `for (@proc_requests){ print join "\t",$_->PID,$_->p->LName,$_->sent_email,"\n"; };` [download] I agree with soonix, your SQL logic is odd. It will retrieve sent_email values of '0000-00-00' because they are defined. poj	[reply] [d/l] [select]
Re^4: Problems Using Join in DBIx::Class by phildeman (Scribe) on Dec 29, 2017 at 02:19 UTC