Laravel Multiple Where And-OR Conditions Scenarios

While building your Laravel query whether using Eloquent Query or DB Query builder, you often have to use multiple where conditions.

Today, we will review the different syntax as well as few examples for it.

Remember: These conditions can be used in any kind of database query - Select, Insert , Update or Delete conditions as well as in sub-queries.

If you don't provide comparison operator in condition, by default = (equals) operator is used.

Let's talk about where() (which is AND condition) and orWhere() conditions:

Laravel multiple where conditions - [AND]:

By default, where conditions are chaining with AND operator. To join via OR operator, you will have to use orWhere which we will talk next.

1) Simple Syntax:

...
	->where('column','operator','value')
	->where('column','operator','value')
...        

Example:

Let's find the user with name = "John Doe" and email = "[email protected] "

$user = User::where('name','John Doe')
              ->where('email','[email protected]')
              ->first();        

2) Array Syntax:

....
->where([
			['column','operator','value'],
			['column','operator','value'],
])
...        

Example:

Let's recreate our above example in grouped form:

$user = User::where([
                    'name','John Doe'],
                    ['email','[email protected]']
		])->first();        

Laravel multiple where conditions - [OR]:

What if you want to use OR condition in your query? You will have to use orWhere() condition for addition query.

Note: First query cannot start with orWhere(). It has to be regular where().

Syntax:

...
	->where('column','operator','value')
	->orWhere('column','operator','value')
...        

Example:

$user = User::where('email','[email protected]')
              ->orWhere('email','[email protected]')
              ->first();        

Above example means - Find me first user whose email is whether '[email protected]' or '[email protected]'

Group Multiple AND-OR-AND where conditions in Parentheses

What if you want to group multiple AND, OR & AND conditions in your query? When dealing with complex queries, you often will come up with this situation.

You cannot chain multiple AND-OR-AND conditions together directly. Remember - In SQL, you will have to use parentheses if you want to group such conditions.

But, in Laravel how would you replicate that?!

? Using Closures

Syntax:

...
->where(function($query) {
		$query->where('column','operator','value')
			->orWhere('column','operator','value');
})
...        

Above, you can see that I have passed a closure function in outer where() condition - It encapsulates the inner conditions in parentheses.

Let's review one example:

Example:

I want to find all users who are active and ( whose email is [email protected] or whose email is [email protected] ) —> [I have used parenthese to demonstrate that no matter what's the case - user should be active]

$users = User::where('active','1')->where(function($query) {
			$query->where('email','[email protected]')
						->orWhere('email','[email protected]');
})->get();        

SQL:

Select * from users where active = '1' and
 (email = '[email protected]' or email = '[email protected]');        

Note: You can nest multiple closures too though make sure to different variable names for each closure.