Re^2: Problems Using Join in DBIx::Class

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'.

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

Replies are listed 'Best First'.
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
Hi Soonix, Thanks for taking the time to review my dilemma. I originally had the DBIx::Class call the way you have it, at the end of your post. However, the where clause you created produces: `WHERE ( status = 'Offered OR status = 'Declined' OR status = 'Cancelled' OR status = 'Conditional Offer' ) OR <-- This must be AND ( sent_email IS NOT NULL AND <-- This must be OR sent_email != '0000-00-00' )` [download] You cannot have a query using one column with 2 different values, unless you use an OR, such as, sent_mail is not null OR sent_email != '0000-00-00'. The other part of the my condition is the status must be one of the four statuses. My current DBIx::Class call produces what I actually need: `WHERE ( sent_email IS NOT NULL OR sent_email != '0000-00-00' ) AND ( status = 'Offer' OR status = 'Declined' OR status = 'Cancelled' OR status = 'Conditional Offer' )` [download] This query will return rows of data with a real sent_email date AND one of the four statuses. FYI, there are more than four statuses, but this part of the condition narrows the list down to these four statuses. Thanks again for taking the time to review my dilemma. -Phil-	[reply] [d/l] [select]
Re^5: Problems Using Join in DBIx::Class by poj (Abbot) on Dec 29, 2017 at 08:50 UTC
The condition you want is sent_mail is NOT ( null OR '0000-00-00' ) which by De Morgan's Laws is sent_mail is ( NOT null AND NOT '0000-00-00' ) The rule is 'the complement of the union of two sets is the same as the intersection of their complements' poj	[reply]
Re^5: Problems Using Join in DBIx::Class by 1nickt (Canon) on Dec 29, 2017 at 04:26 UTC
Hi, as soonix and poj have said your query appears to be wrong, or not doing what you say you want. A sent_email date of '0000-00-00', which you do not want, will match `IS NOT NULL` in the first conditional and thus be returned. You cannot have a query using one column with 2 different values, unless you use an OR That's not correct. You use an arrayref, but the first element must be a modifier, e.g. `-and`. I would say you need the following rather simpler construct: `my %where = ( status => ['Offered','Denied','Cancelled','Conditional Offer'], sent_email => { '!=' => [ -and => undef, '0000-00-00' ] }, );` [download] Demonstrated with SQL::Abstract (which is used internally by DBIx::Class): `use strict; use warnings; use feature 'say'; use Data::Dumper; use SQL::Abstract; my %where = ( status => ['Offered','Denied','Cancelled','Conditional Offer'], sent_email => { '!=' => [ -and => undef, '0000-00-00' ] }, ); my $sqla = SQL::Abstract->new; my ( $sql, @bind ) = $sqla->select( 'TblRequests', 'sent_email', \%where, undef, ); say $sql; say Dumper \@bind; __END__` [download] Output: `SELECT sent_email FROM TblRequests WHERE ( ( ( sent_email IS NOT NULL +AND sent_email != ? ) AND ( status = ? OR status = ? OR status = ? OR + status = ? ) ) ) $VAR1 = [ '0000-00-00', 'Offered', 'Denied', 'Cancelled', 'Conditional Offer' ];` [download] Hope this helps! The way forward always starts with a minimal test.	[reply] [d/l] [select]
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
Thanks poj. That was it. I overlooked that. -Phil-	[reply]