DBIx::Class Build a Where Clause with multiple ORs and ANDs

[phildeman]

Hi,

I am unsuccessfully trying to build a Where Clause in DBIx::Class.
The where clause should be:

WHERE status='Review' OR
              ( ( status='Offered' OR
                   status='Denied' OR
                   status='Cancelled' OR
                   status='Conditional Offer') AND
                 ( sent_email is NULL OR
                   sent_email='0000-00-00')
             )

I was successful able to produce this Where Clause:

WHERE ( status='Offered' OR
                status='Denied' OR
                status='Cancelled' OR
                status='Conditional Offer') AND
              ( sent_email is NULL OR
                sent_email='0000-00-00');

using this snippet of code:

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'}
+,
                                ],
                            ],
                });

All I need is to add is the outer OR for status = 'Review'. Any thoughts on how to
write a DBIx::Class where clause to produce the first Where Clause at the top?

Thanks

[1nickt]

Hi, since you've been struggling with this stuff for a long time now, I strongly suggest again that you spend some time experimenting with SQL::Abstract, which is the SQL generator used by DBIx::Class.

Use arrays for ORs; use hashes for ANDs. Until your query gets complicated, you can skip the -and and -or keywords.

Updated: Based on the correction you made to your spec.

use strict; use warnings;
use SQL::Abstract;

my $sqla = SQL::Abstract->new;

my @where = (
    { status => 'Review' },
    {
      status     => [ 'Offered', 'Denied', 'Cancelled', 'Conditional O
+ffer' ],
      sent_email => { '!=' => [ -and => undef, '0000-00-00' ] },
    },
);

my ( $sql, @bind ) = $sqla->where( \@where );

say $sql;
say for @bind;

__END__

Output:

perl 1206574.pl

 WHERE ( ( status = ? OR ( ( sent_email IS NOT NULL AND sent_email != 
+? ) AND ( status = ? OR status = ? OR status = ? OR status = ? ) ) ) 
+)
Review
0000-00-00
Offered
Denied
Cancelled
Conditional Offer

Used with DBIx::Class:

$schema->resultset( 'TblRequests' )->search([
    { status => 'Review' },
    {
      status     => [ 'Offered', 'Denied', 'Cancelled', 'Conditional O
+ffer' ],
      sent_email => { '!=' => [ -and => undef, '0000-00-00' ] },
    },
]);

Hope this helps!

[phildeman]

Hi 1nickt,

Thanks for your help! Much appreciated.

I was really having issues with when to use the hashes and arrays. The DBIx::Class
documentation wasn't clear on that. This helped me greatly.

Thanks again, for your help.

-Phil-

[poj]

I was successful able to produce this Where Clause:

( sent_email is NULL OR
  sent_email='0000-00-00');

using this snippet of code:

  sent_email => {'!=', undef },
  sent_email => {'!=', '0000-00-00

Are you sure, the code logic is NOT equal ? Try

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

poj

[phildeman]

Hi poj,

Thanks for your response. The send_email => {'!=' , ....} was deliberate.
I do not want send_email to be undefined or have a value of '0000-00-00', when
status is equal to Offered, Denied, Cancelled, or Conditional Offer.

However, if the status equals Review, the sent_email can have any value.

-Phil-

[poj]

This logic is always TRUE

 -or => [
      sent_email => {'!=', undef },
      sent_email => {'!=', '0000-00-00'}
  ],

for it to be FALSE the sent_email must be both undefined AND '0000-00-00' which is not possible. I think you want

-or =>[ status => 'Review' ,  
    -and => [ 
       -or => [
         status => 'Offered',
         status => 'Denied',
         status => 'Cancelled',
         status => 'Conditional Offer',
       ],
       sent_email => {'!=', undef },
       sent_email => {'!=', '0000-00-00'},
     ],
   ],  

poj

[1nickt]

I do not want send_email to be undefined or have a value of '0000-00-00', when status is equal to Offered, Denied, Cancelled, or Conditional Offer.

However, if the status equals Review, the sent_email can have any value.

But in your initial example in your OP you said:

The where clause should be:

WHERE status='Review' OR
              ( ( status='Offered' OR
                   status='Denied' OR
                   status='Cancelled' OR
                   status='Conditional Offer') AND
                 ( sent_email is NULL OR
                   sent_email='0000-00-00')
             )

I showed you how to achieve the outcome described in your most recent comment in an earlier thread. I've updated my response to your initial query today now that you've clarified your spec.