Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

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

by phildeman (Beadle)
on Dec 29, 2017 at 03:11 UTC ( #1206387=note: print w/replies, xml ) Need Help??


in reply to Re^3: Problems Using Join in DBIx::Class
in thread Problems Using Join in DBIx::Class

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

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

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-

Replies are listed 'Best First'.
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
Re^5: Problems Using Join in DBIx::Class
by 1nickt (Abbot) 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' ] }, );
    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__
    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' ];

    Hope this helps!


    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: note [id://1206387]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (4)
As of 2019-03-22 06:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    How do you Carpe diem?





    Results (110 votes). Check out past polls.

    Notices?