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

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]

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

(at least this looks more "logical" to me)

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-

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

Thanks poj. That was it. I overlooked that. -Phil-