Where Clauses in Laravel 5

The Where clause is used to extract only those records that fulfill a specified condition. 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 “id” column is equal to 6 using “tbl_order” table

$order = DB::table('tbl_order')->where('id', '=', 5)->get();

Another type to used where clause in laravel 5

$order = DB::table('tbl_order')->where('id',5)->get();

Many other operators when writing a where clause

$order = DB::table('tbl_order')
      ->where('amount', '>=', 100)
      ->get();

$order = DB::table('tbl_order')
      ->where('amount', '<=', 100)
      ->get();

$order = DB::table('tbl_order')
      ->where('amount', '!=', 100)
      ->get();

$order = DB::table('tbl_order')
      ->where('amount', '<>', 100)
      ->get();

$order = DB::table('tbl_order')
      ->where('name', 'like', 's%')
      ->get();

If you want to use array pass in where condition, you can check below example

$order = DB::table('tbl_order')->where([
            ['status', '=', '1'],
            ['amount', '>=', '100'],
          ])->get();

Or Statements

$order = DB::table('tbl_order')
      ->where('amount', '>=', 100)
      ->orWhere('name', 'samsung')
      ->get();

Additional Where Clauses

whereBetween

The whereBetween method finds that a column’s value is between two values.

$order = DB::table('tbl_order')
          ->whereBetween('amount', [1, 100])->get();

whereNotBetween

The whereNotBetween method finds that a column’s value lies outside of two values.

$order = DB::table('tbl_order')
          ->whereNotBetween('amount', [50, 500])->get();

whereIn / whereNotIn

The whereIn method work with array and find specific column

$order = DB::table('tbl_order')
          ->whereIn('id', [10,11,12])->get();

The whereNotIn method finds that the given column’s value is not contained in the given array

$order = DB::table('tbl_order')
          ->whereNotIn('id', [10,11,12])->get();

whereNull / whereNotNull

The whereNull method verifies that the value of the given column is NULL.

$order = DB::table('tbl_order')
          ->whereNull('created_at')
          ->get();

The whereNotNull method verifies that the column’s value is not NULL.

$order = DB::table('tbl_order')
          ->whereNotNull('created_at')
          ->get();

whereDate / whereMonth / whereDay / whereYear

$order = DB::table('tbl_order')
          ->whereDate('created_at', '2017-12-31')
          ->get();

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

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

$order = DB::table('tbl_order')
          ->whereYear('created_at', '2017')
          ->get();

whereColumn

The whereColumn method may be used to verify that two columns are equal.

$order = DB::table('tbl_order')
      ->whereColumn('product_code', 'product_name')
      ->get();

If you want to use comparison operator in this method.

$order = DB::table('tbl_order')
          ->whereColumn('amount', '>', 'grand_amount')
          ->get();

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

$users = DB::table('tbl_order')
                ->whereColumn([
                    ['product_code', '=', 'product_name'],
                    ['amount', '>', 'grand_amount']
                ])->get();

Parameter Grouping

Sometimes you may need to create more advanced where clauses such as “where exists” clauses or nested parameter groupings. The Laravel query builder can handle these as well.

DB::table('tbl_order')
            ->where('product_name', '=', 'diamond')
            ->orWhere(function ($query) {
                $query->where('amount', '>', 100)
                      ->where('payment', '<>', 'paypal');
            })
            ->get();

select * from tbl_order where product_name = 'diamond' or 
(amount > 100 and payment <> 'paypal');

Where Exists Clauses

The whereExists method allows you to write where exists SQL clauses.

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

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

JSON Where Clauses

Laravel also supports querying JSON column types on databases that provide support for JSON column types. Currently, this includes MySQL 5.7 and Postgres. To query a JSON column, use the -> operator.

$order = DB::table('tbl_order')
                ->where('payment->language', 'paypal')
                ->get();

$order = DB::table('tbl_order')
                ->where('payment->language->paypal', 'pay')
                ->get();