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.

Related Blogs

Delete an element from an array in PHP

Delete an element from an array in PHP

To delete an element from an array in PHP, use built-in functions unset(), array_splice(), or array_diff(). Learn how to delete elements using these functions.

laravel sort by pivot table field

Laravel Order by Pivot Table Field

Learn how to order results by pivot table field in eloquent. Two methods 1) using orderBy in Eager loading 2) Using orderByPivot in lazy loading

Limit Text in Laravel

Limit Text in Laravel

Limit text in Laravel can be done using the Str class from Illuminate\Support\Str namespace for truncating text by character count and word count.

419 page expired Laravel error

419 page expired error in Laravel

419 page expired error in Laravel occurs when the valid CSRF token is missing in the post request. Laravel checks the CSRF token in VerifyCsrfToken middleware.

What's New in PHP 8

What's New in PHP 8(Features, Changes, Security & JIT compiler)

PHP 8 new features and functions include named arguments, union types, attributes, constructor property promotion, match expression, nullsafe operator, Saner string to number comparisons, Saner Numeric Strings, and JIT Compiler.

Regular Expression

Most Common Regular Expressions - email, URL, strong password, credit cards, number systems and dates

Regular expressions for email, URL, strong password, credit cards, number systems, dates and more.

Can't Perform a React State Update on an Unmounted Component

Can't Perform a React State Update on an Unmounted Component

React Warning “Can't perform a react state update on an unmounted component” is caused when we try to update the state after the component was unmounted. Explains how to fix it.

Each child in a list should have a unique key prop

Each child in a list should have a unique key prop

Solve Warning: Each child in a list should have a unique ”key" prop in React by setting the id property as a unique key or by auto-assigning unique keys.

React Hook is Called Conditionally

React Hook is Called Conditionally

Error: "React Hook is called conditionally. React Hooks must be called in the exact same order in every component render" occurs when hooks are invoked conditionally or after a return of a value.