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

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

Stacey1134Stacey1134 Posts: 112Questions: 20Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,970Questions: 87Answers: 421
    Answer ✓

    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 );
        });
    })
    
  • rf1234rf1234 Posts: 2,970Questions: 87Answers: 421
    edited March 15

    In SQL it would look like this:

    WHERE field_1 = 1
      AND ( field_2 <> 'fubar' OR
           ( field_2 = 'fubar' AND
             field_3 = 1           ) )
    
  • rf1234rf1234 Posts: 2,970Questions: 87Answers: 421

    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           )
    
  • rf1234rf1234 Posts: 2,970Questions: 87Answers: 421

    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
    
  • Stacey1134Stacey1134 Posts: 112Questions: 20Answers: 0

    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.

  • rf1234rf1234 Posts: 2,970Questions: 87Answers: 421

    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.

  • Stacey1134Stacey1134 Posts: 112Questions: 20Answers: 0

    @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....

  • rf1234rf1234 Posts: 2,970Questions: 87Answers: 421

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

  • allanallan Posts: 63,434Questions: 1Answers: 10,458 Site admin

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

    Allan

  • Stacey1134Stacey1134 Posts: 112Questions: 20Answers: 0

    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.

  • rf1234rf1234 Posts: 2,970Questions: 87Answers: 421

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

    That is certainly a good point!
    Good luck!

Sign In or Register to comment.