Multiple "And" and "OR where" clauses in eloquent query

In this article we will take a look on how we can build SQL queries with multiple 'AND' and 'OR' where statements using Eloquent ORM.

Let first start with the simple Where Clauses
You may use the query builder's where method to add "where" clauses to the query. The most basic call to the where method requires three arguments.

The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. The third argument is the value to compare against the column's value.

For example, the following query retrieves users where the value of the city column is equal to 'Mumbai' and the value of the age column is greater than 35:


$users = DB::table('users')
                ->where('city','Mumbai')
                ->where('age', '>', 35)
                ->get();

When chaining together where method, the "where" clauses will be joined together using the 'and' operator. For 'or ' condition 'orWhere ' is used in query builder.

So if we want get results from 'users table' for 'or' condition say age is 25 or status is married then query will be like


$users = DB::table('users')
                    ->where('age', '=', 25)
                    ->orWhere('status', 'married')
                    ->get();

For the following SQL:
select * from users where city = 'Mumbai' or (age = '25' and status = 'married')

If you need to group an "or" condition within parentheses, you may pass a closure as the first argument to the 'orWhere' method:


$users = DB::table('users')
            ->where('city','Mumbai')
            ->orWhere(function($query) {
                $query->where(''age', '=', 25)
                       ->where('status', 'married');
            })->get();

Similarly we can group an "and" condition , you may pass a closure as the first argument to the 'Where' method:


$users = User::where('active','1')
         ->where(function($query) {
	        $query->where('city','Mumbai')
	                ->orWhere('city','London');
})->get();

The example above will produce the following SQL:

select * from users where active = '1' and (city = 'Mumbai' or city = 'London');

So here is simple explanation of combining multiple "and" and "or" where clauses.

Comments