May 11, 2017

Laravel Database Where Clauses

Laravel Database Where Clauses – The Laravel Database Where Clauses is used to compare that a column is equal to given value.

Laravel Database Where Clauses.

Let us understand how to use Laravel Database Where Clauses.

Function:-

There are some followings methods available in Laravel Database Where Clauses.

Simple Where Clauses

You can use the where method on a query builder instance to add where clauses to the query. The where method required three arguments. First is name of the column. Second argument is an operator. finally third argument is the value to evaluate against the column.

$users = DB::table('users')->where('votes', '=', 100)->get();

If you want simply to verify that a column is equal to given value we can use like this:-

$users = DB::table('users')->where('votes', 100)->get();

You can also use like this:-

$users = DB::table('users')
                ->where('votes', '>=', 100)
                ->get();

$users = DB::table('users')
                ->where('votes', '<>', 100)
                ->get();

$users = DB::table('users')
                ->where('name', 'like', 'T%')
                ->get();

You can also pass an array of condition to the where method.

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

Or Statements

The orWhere method accept the same arguments as the where method.

$users = DB::table('users')
                    ->where('votes', '>', 100)
                    ->orWhere('name', 'John')
                    ->get();

Additional Where Clauses

whereBetween

The whereBetween method verify that a column value is between two values.

$users = DB::table('users')
                    ->whereBetween('votes', [1, 100])->get();

whereNotBetween

The whereNotBetween verify that a column value lies outside of two values.

$users = DB::table('users')
                    ->whereNotBetween('votes', [1, 100])
                    ->get();

whereIn / whereNotIn

The whereIn method verify that a given column values is contained within the given array.

$users = DB::table('users')
                    ->whereIn('id', [1, 2, 3])
                    ->get();

The whereNotIn method verify that a given column values is not contained within the given array.

$users = DB::table('users')
                    ->whereNotIn('id', [1, 2, 3])
                    ->get();

whereNull / whereNotNull

The whereNull method verify that the value of given column is NULL

$users = DB::table('users')
                    ->whereNull('updated_at')
                    ->get();

The whereNotNull method verify that the value of given column is not NULL

$users = DB::table('users')
                    ->whereNotNull('updated_at')
                    ->get();

whereDate / whereMonth / whereDay / whereYear

The whereDate method can be used to compare column value against a date.

$users = DB::table('users')
                ->whereDate('created_at', '2016-12-31')
                ->get();

The whereMonth method can be used to compare column value against a month of the year.

$users = DB::table('users')
                ->whereMonth('created_at', '12')
                ->get();

The whereDay method can be used to compare column value against a specific day of a month.

$users = DB::table('users')
                ->whereDay('created_at', '31')
                ->get();

The whereYear method can be used to compare column value against a specific year.

$users = DB::table('users')
                ->whereYear('created_at', '2016')
                ->get();

whereColumn

The whereColumn method can be used to verify that two column are equals.

$users = DB::table('users')
                ->whereColumn('first_name', 'last_name')
                ->get();

You can also pass by using comparison operator.

$users = DB::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

The whereColumn method can also be passed an array of multiple condition.

$users = DB::table('users')
                ->whereColumn([
                    ['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at']
                ])->get();

Parameter Grouping

Sometimes you may need to create more advanced where clauses such as “where exists” clauses or nested parameter groupings.

DB::table('users')
            ->where('name', '=', 'John')
            ->orWhere(function ($query) {
                $query->where('votes', '>', 100)
                      ->where('title', '<>', 'Admin');
            })
            ->get();

As we can see, passing the closure into the orWhere method instructs the query builder to begin a constraint group.

select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

Where Exists Clauses

This method allow you to write where exists SQL clauses. The whereExists
method accepts a closure argument, which will recieve a query builder instance allowing you to define the query that should be placed inside of the “exists” clause.

DB::table('users')
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                      ->from('orders')
                      ->whereRaw('orders.user_id = users.id');
            })
            ->get();

This query will be produce the following SQL.

select * from users
where exists (
    select 1 from orders where orders.user_id = users.id
)

JSON Where Clauses

Laravel supports querying JSON column types on databases which provide support for JSON column types. Currently, this includes MySQL 5.7 and Postgres.

$users = DB::table('users')
                ->where('options->language', 'en')
                ->get();

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();

Laravel Database Where Clauses