Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

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

by phildeman (Beadle)
on Jan 03, 2018 at 04:45 UTC ( #1206574=perlquestion: print w/replies, xml ) Need Help??
phildeman has asked for the wisdom of the Perl Monks concerning the following question:

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

Replies are listed 'Best First'.
Re: DBIx::Class Build a Where Clause with multiple ORs and ANDs
by 1nickt (Monsignor) on Jan 03, 2018 at 13:40 UTC

    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!


    The way forward always starts with a minimal test.

      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-

Re: DBIx::Class Build a Where Clause with multiple ORs and ANDs
by poj (Monsignor) on Jan 03, 2018 at 08:32 UTC
    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

      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-

        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

        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.


        The way forward always starts with a minimal test.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1206574]
Front-paged by Corion
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (6)
As of 2018-07-21 13:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    It has been suggested to rename Perl 6 in order to boost its marketing potential. Which name would you prefer?















    Results (449 votes). Check out past polls.

    Notices?