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.
On datainfinities.com, Read articles all around JavaScript, React, Node.js, PHP, Laravel, and Shopify.
Related Blogs
How to display validation error messages in Laravel blade view
Laravel Cron Job Task Scheduling Tutorial
Delete an element from an array in PHP
Get raw SQL query from Laravel Query Builder
419 page expired error in Laravel
The POST method is not supported for this route. Supported methods: GET, HEAD
Laravel Model Events and Observers Tutorial
How to solve error: array_merge() does not accept unknown named parameters
Explore All Blogs