How To Use Multiple Where Clause In Query Using Laravel

admin_img Posted By Bajarangi soft , Posted On 23-09-2020

WHERE clause is used to filter records. WHERE clause is used to extract only those records that fulfill a specified condition.WHERE clause returns the records which are present in the table. So, this WHERE clause can use in Laravel framework. There is more benefits of WHERE clause condition.

How Can I Use Multiple Where Clause Queries with Laravel

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();

Related Post