Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

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

by 1nickt (Monsignor)
on Jan 03, 2018 at 13:40 UTC ( #1206600=note: print w/replies, xml ) Need Help??


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

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.

Replies are listed 'Best First'.
Re^2: DBIx::Class Build a Where Clause with multiple ORs and ANDs
by phildeman (Beadle) on Jan 03, 2018 at 16:30 UTC

    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-

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1206600]
help
Chatterbox?
[Discipulus]: April: the Perl Tk official month..

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (3)
As of 2018-04-24 07:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?