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

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)

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

Replies are listed 'Best First'.
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]