YouTube Icon

Code Playground.

How to Use Where Condition in Laravel 5

CFG

How to Use Where Condition in Laravel 5

The Where clause is used to extract only those records which fulfill a condition stated. The most simple call to where three arguments are required. The first statement is the column name. The second argument is an operator that can be supported by any of the operators in the database. Ultimately, the third argument is the interest against the column to be measured.

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




CFG