Laravel multiple where clause checking for null values through a variable

Question

I am storing a where clause in a variable

if($condition === 1) {
    $whereClause = ['mID' => NULL, 'qid' => $listing->lID, 'deleted' => false];
}
else {
    $whereClause= ['mID' => $member->mID, 'qid' => $listing->lID, 'deleted' => false];
}

And I get the collection via

$collection = Model::where($whereClause)

BUT on the first condition, I actually want the mID to be either 0 or NULL, I want to retrieve items where mID can be 0 or NULL, so I want orWhere clause, which I did use as shown below, but this does not work, gives an error of memory exhausted so something went wrong

if($condition === 1) {
    $whereClause = ['mID' => NULL, 'qid' => $listing->lID, 'deleted' => false];
    $orWhereClause = ['mID' => 0, 'qid' => $listing->lID, 'deleted' => false];

     $collection = Model::where($whereClause)->orWhere($orWhereClause)

}
else {
    $whereClause= ['mID' => $member->mID, 'qid' => $listing->lID, 'deleted' => false];
    $collection = Model::where($whereClause)

}

Any ideas of how I can achieve this sort of where condition

miken32 · Accepted Answer · 2019-11-14 20:53:00Z

2

This seems like the easiest method. Chaining where() methods is the same as passing them in as an array.

$collection = Model::where('qid', $listing->lID)->where('deleted', false);

if($condition === 1) {
    $collection = $collection->whereIn('mID', [0, NULL]);
}
else {
    $collection = $collection->where('mID', $member->mID);
}

(Note that according to the documentation, if you wanted to pass an array to the where() method, you were doing it wrong.)

edited Nov 14, 2019 at 20:53

answered Nov 14, 2019 at 20:47

miken32

42.5k16 gold badges127 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alberto · Accepted Answer · 2019-11-14 21:07:24Z

2

Except for the missing of ";", i think that you should implement it in this way:

$whereClause = [['mID', NULL], ['qid' , $listing->lID] , ['deleted', false]];
$orWhereClause = [['mID', 0], ['qid' , $listing->lID] , ['deleted', false]];
$collection = Model::where($whereClause)->orWhere($orWhereClause);

and if you need other condition, just push an array in the right array where the first parameter is the field and the second the value, or first parameter is the field, the second the operator and the third the value

edited Nov 14, 2019 at 21:07

answered Nov 14, 2019 at 20:46

Alberto

13.1k3 gold badges31 silver badges72 bronze badges

3 Comments

miken32 Over a year ago

This will include any records that have mID = 0 regardless of whether deleted = false or qid = $linting->lID.

Alberto Over a year ago

@miken32 edited, please try and tell me what happens

Alauddin Afif Cassandra Over a year ago

amazing! workwell. thankyou!

N Mahurin · Accepted Answer · 2019-11-14 21:05:53Z

1

Miken32 is correct, but the code could be written in a single query instead of adding the where() in an if/else statement:

$collection = Model::where('qid', $listing->lID)->where('deleted', false)
->when($condition === 1, function($q){
    $q->whereIn('mID', [0, NULL]);
})->when($condition !== 1, function($q){
    $q->where('mID', $member->mID);
});

answered Nov 14, 2019 at 21:05

N Mahurin

1,45611 silver badges18 bronze badges

Collectives™ on Stack Overflow

Laravel multiple where clause checking for null values through a variable

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related