Where Clause using And and For including an exclude if another field has certain value

Stacey1134

Okay, I finally will swallow my pride and ask this question. I am having the darndest time with complex where clauses using Editor, and a have not been able to find a close answer in the docs.

So here's the premise. I want to include rows where field_1 = 1. I also want to exclude rows where field_2 = 'fubar'. Except, I want to include rows where field_2 = 'fubar' if field_3 = 1.

This is where I finally got to but it doesn't work

//  ->where( 'field_2', 'fubar', '!=')
//  ->where( 'field_1', 1 )

    ->where( function ( $q ) {
        $q
        ->where( 'field_1', 1 )
        ->where( 'field_2', 'fubar', '!=')
        ->where( function ( $r) {
            $r->or_where( 'field_2', 'fubar' );
            $r->where ( 'field_3', 1 );
        });
    })

If someone could tell me where I am going wrong here it would be of great help. Thanks

rf1234

This should work:

->where( 'field_1', 1 ) 
->where( function ( $q ) {
    $q->where( 'field_2', 'fubar', '!=')
    $q->or_where( function ( $r) {
        $r->where( 'field_2', 'fubar' );
        $r->where ( 'field_3', 1 );
    });
})

rf1234

In SQL it would look like this:

WHERE field_1 = 1
  AND ( field_2 <> 'fubar' OR
       ( field_2 = 'fubar' AND
         field_3 = 1           ) )

rf1234

I want to include rows where field_1 = 1. I also want to exclude rows where field_2 = 'fubar'. Except, I want to include rows where field_2 = 'fubar' if field_3 = 1.

This could be simplified to:

I want to include rows where field_1 = 1. I also want to include rows where field_2 is not = 'fubar' or field_3 = 1.

which is

WHERE field_1 = 1
  AND ( field_2 <> 'fubar' OR
        field_3 = 1           )

rf1234

This is a nice one because it shows how imprecise our language is.

The text above could also be interpreted to mean something very simple. Because it basically says "I want to include a and also want to include b or c". That means in boolean algebra "a or b or c".

WHERE field_1 = 1 OR field_2 <> 'fubar' OR field_3 = 1

Stacey1134

I will test this this afternoon: But this simplification does not seem correct

I want to include rows where field_1 = 1. I also want to include rows where field_2 is not = 'fubar' or field_3 = 1.

As I don't want to include field_2 if it equals fubar and field_3 does not = 1. But as you say, it's probably the English language. Keeping my fingers crossed.

rf1234

But as you say, it's probably the English language. Keeping my fingers crossed

I guess this is universal. My language is German. And George Boole was an Irishman.

Stacey1134

@rf1234

->where( 'field_1', 1 )
->where( function ( $q ) {
    $q->where( 'field_2', 'fubar', '!=');
    $q->or_where( function ( $r) {
        $r->where( 'field_2', 'fubar' );
        $r->where ( 'field_3', 1 );
    });
})

worked great! Thanks only syntax change was adding the semicolon at the end of line three. Thanks!

But now SearchBuilder doesn't correctly work anymore. ARGH! Now to figure this out....

rf1234

But now SearchBuilder doesn't correctly work anymore. ARGH! Now to figure this out....

I guess now you would need expert help! Allan or Kevin may be able to help you, I think. Please post details on the error messages you get (console).

allan

In what way does it not work? Too many options show, search not being applied, errors, or something else?

Allan

Stacey1134

Hey not posting a problem on SearchBuilder because it definitely is my problem when forcing complex where clauses, server side first. No error messages anyway. Right now I'm back to figuring out searchBuilder not working with date ranges, but I posted that in January. So if I need further help I will request it on that thread.

rf1234

Hey not posting a problem on SearchBuilder because it definitely is my problem

That is certainly a good point!
Good luck!