Simple Where Clauses
You may use the where
method on a query builder instance to add where
clauses to the query. The most basic call to where
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. Finally, the third argument is the value to evaluate against the column.
For example, here is a query that verifies the value of the "name" column is equal to 'Kiran':
$users = DB::table('users')->where('name', '=', 'kiran)->get();
you may pass the value directly as the second argument to the where
method:
$users = DB::table('users')->where('name', 'kiran')->get();
We can use a variety of other operators when writing a where
clause:
$employee = DB::table('employee')
->where('salary', '>=', 10000)
->get();
$employee = DB::table('employee')
->where('salary', '<>', 10000)
->get();
$employee = DB::table('employee')
->where('salary', 'like', 'T%')
->get();
Can also pass an array of conditions to the where
function:
$employee = DB::table('employee')->where([
['id', '=', '1'],
['salary', '<>', '10000'],
])->get();
Or Statements
We can chain where constraints together as well as add or
clauses to the query. The orWhere
method accepts the same arguments as the where
method:
$employee = DB::table('employee ')
->where('id', '>', 100)
->orWhere('name', 'kiran')
->get();
If we need to group an "or" condition within parentheses, you may pass a Closure as the first argument to the orWhere
method:
$employee = DB::table('employee')
->where('id', '>', 100)
->orWhere(function($query) {
$query->where('name', 'kiran')
->where('id', '>', 50);
})
->get();
// SQL: select * from users where id > 100 or (name = 'kiran' and id > 50)
Additional Where Clauses
whereBetween / orWhereBetween
The whereBetween
method verifies that a column's value is between two values:
$employee = DB::table('employee')
->whereBetween('id', [1, 100])
->get();
whereNotBetween / orWhereNotBetween
The whereNotBetween
method verifies that a column's value lies outside of two values:
$employee = DB::table('employee')
->whereNotBetween('id', [1, 100])
->get();
whereIn / whereNotIn / orWhereIn / orWhereNotIn
The whereIn
method verifies that a given column's value is contained within the given array:
$employee = DB::table('employee')
->whereIn('id', [1, 2, 3])
->get();
The whereNotIn
method verifies that the given column's value is not contained in the given array:
$employee = DB::table('employee')
->whereNotIn('id', [1, 2, 3])
->get();
whereNull / whereNotNull / orWhereNull / orWhereNotNull
The whereNull
method verifies that the value of the given column is NULL
:
$employee = DB::table('employee')
->whereNull('updated_at')
->get();
The whereNotNull
method verifies that the column's value is not NULL
:
$employee = DB::table('employee')
->whereNotNull('updated_at')
->get();
whereDate / whereMonth / whereDay / whereYear / whereTime
The whereDate
method may be used to compare a column's value against a date:
$employee = DB::table('employee')
->whereDate('created_at', '2020-09-02')
->get();
The whereMonth
method may be used to compare a column's value against a specific month of a year:
$employee = DB::table('employee')
->whereMonth('created_at', '02')
->get();
The whereDay
method may be used to compare a column's value against a specific day of a month:
$employee = DB::table('employee')
->whereDay('created_at', '31')
->get();
The whereYear
method may be used to compare a column's value against a specific year:
$employee = DB::table('employee')
->whereYear('created_at', '2020')
->get();
The whereTime
method may be used to compare a column's value against a specific time:
$employee = DB::table('employee')
->whereTime('created_at', '=', '15:43:45')
->get();
whereColumn / orWhereColumn
The whereColumn
method may be used to verify that two columns are equal:
$employee = DB::table('employee')
->whereColumn('first_name', 'last_name')
->get();
You may also pass a comparison operator to the method:
$employee = DB::table('employee')
->whereColumn('updated_at', '>', 'created_at')
->get();
The whereColumn
method can also be passed an array of multiple conditions. These conditions will be joined using the and
operator:
$employee = DB::table('employee')
->whereColumn([
['first_name', '=', 'last_name'],
['updated_at', '>', 'created_at'],
])->get();