How to create multiple where clause query using Laravel eloquent

In Laravel eloquent, you can add multiple where clauses by chaining the where() query builder function. When chaining the where() method, the where clauses will be joined by the AND operator. if you want to join by OR operator, use orWhere() query builder function.

where method

The where methods join the clauses together with the AND operator. The syntax for where condition is:

->where('COLUMN_NAME', 'OPERATOR', 'VALUE')

You can write multiple where clause queries as below.

DB::table('cars')->where('color', '=', 'red')
	  			  ->where('price', '<', '100000')
	  			  ->where('mileage', '>', '15')
	  			  ->get();	

If operator is ‘=’, when you can ignore ‘=’ operator and above code will become:

DB::table('cars')->where('color', 'red') // '=' operator is removed in this line
	  			  ->where('price', '<', '100000')
	  			  ->where('mileage', '>', '15')
	  			  ->get();	

Multiple where clauses can be passed as an array. Each where condition in the array should be another array containing the three arguments as given below syntax.

->where([
    ['COLUMN_NAME','OPERATOR','VALUE'],
    ['COLUMN_NAME','OPERATOR','VALUE'],
])

So our example can be rewritten as:

DB::table('cars')->where([
        ['color', '=', 'red'],
        ['price', '<', '10000'],
        ['mileage', '>', '15']
    ])->get();

orWhere Method

If you may want to join with the OR operator then, You can use the orWhere method with the same syntax as where method.

->orWhere('COLUMN_NAME', 'OPERATOR', 'VALUE')

If you want to group an "or" condition within parentheses, you can write the orWhere method as given below:

DB::table('cars')->where('color', 'red')
                        ->where('price', '<', '100000')
                        ->where('mileage', '>', '15')
                        ->orWhere(function($query) {
                            $query->where('fuel_type', 'Diesel')
                                ->where('mileage', '>', 10);
                        })
                        ->get();

The above code will generate SQL query as:

SELECT * FROM cars WHERE color = 'red' AND price < 100000 AND mileage > 15 OR ( fuel_type = 'Diesel' AND mileage > 10)

whereNot Method

The whereNot and orWhereNot method is used to implement the NOT command with WHERE in the SQL using Laravel Eloquent. Look at the below query, how whereNot can be implemented.

DB::table('cars')
	->whereNot(function ($query) {
		$query->where('transmission', 'automatic')
			  ->orWhere('seating_capacity', '<', 5);
		})
	->get();

The above query will not select cars with transmission as ‘automatic’ and seating_capacity less than 5.

You can also use the below methods in addition to the where and orWhere methods.

• whereNull - used to check the value of the given column is NULL
• whereBetween - used to verify column value is between two given values
• whereIn - used to check column value is in given array values
• whereColumn - used to check given two columns are equal
• whereMonth - used to compare a column's value against a specific month
• whereDay - used to compare a column's value against a specific day of the month
• whereYear - used to compare a column's value against a specific year
• whereTime - used to compare a column's value against a specific time 

For more where clauses see laravel doc.

Conclusion

You can add multiple where clauses by chaining the where() method. By chaining, the clauses will be joined by the AND operator. If you want to join with the OR operator, use the orWhere method. The whereNot, whereNull, whereIn, whereColumn, etc.. can be used in addition to the above methods to create SQL queries with multiple and complex where conditions.