Laravel Eloquent: Add filter in where (if-else for Conditional Queries)?

Question

I have this code :

$result = DB::table('product as p')
      ->join('master as cl', 'p.color', '=', 'cl.name')
      ->join('master as brand', 'p.brand', '=', 'brand.name')
      ->where('p.brand', $brandname)
      ->whereRaw("p.del_flag = 0  and cl.is_active = 1 and brand.is_active = 1 and p.product_type = 1")
      ->select('p.name')
      ->groupBy('p.name')
      ->orderBy('p.name')
      ->take(3)
      ->get();

I need add filters:

if(isset($_POST['brand'])) {
    ->where('p.brand', $_POST['brand'])
}
if(isset($_POST['color'])) {
    ->where('p.color', $_POST['color'])
}
if(isset($_POST['product_type'])) {
    ->where('p.product_type', $_POST['product_type'])
}

I've seen a lot of posts regarding multiple Where in laravel but not a single one talking about those special wheres.

how to add filter in query?

Try this laravel.com/docs/5.7/queries#conditional-clauses

Amol Rokade
– Amol Rokade

2018-12-13 09:37:25 +00:00
Commented Dec 13, 2018 at 9:37 — Amol Rokade
– Amol Rokade, Commented Dec 13, 2018 at 9:37

Jerodev · Accepted Answer · 2018-12-13 10:00:45Z

6

First pass the query to a variable and update the query variable.

$query = DB::table('product as p')
      ->join('master as cl', 'p.color', '=', 'cl.name')
      ->join('master as brand', 'p.brand', '=', 'brand.name')
      ->where('p.brand', $brandname)
      ->whereRaw("p.del_flag = 0  and cl.is_active = 1 and brand.is_active = 1 and p.product_type = 1")
      ->select('p.name')
      ->groupBy('p.name')
      ->orderBy('p.name')
      ->take(3);

if( isset($_POST['brand'])) {
    $query->where('p.brand', $_POST['brand'])
}
if (isset($_POST['color'])) {
    $query->where('p.color', $_POST['color'])
}
if (isset($_POST['product_type'])) {
    $query->where('p.product_type', $_POST['product_type'])
}

$result = $query->get();

Update:
As @Amol Rokade mentioned, you can also use the when() function:

$result = DB::table('product as p')
      ->join('master as cl', 'p.color', '=', 'cl.name')
      ->join('master as brand', 'p.brand', '=', 'brand.name')
      ->where('p.brand', $brandname)
      ->whereRaw("p.del_flag = 0  and cl.is_active = 1 and brand.is_active = 1 and p.product_type = 1")
      ->when(isset($_POST['brand']), function ($query) {
            $query->where('p.brand', $_POST['brand']);
      })
      ->when(isset($_POST['color']), function ($query) {
            $query->where('p.color', $_POST['color']);
      })
      ->when(isset($_POST['product_type']), function ($query) {
            $query->where('p.product_type', $_POST['product_type']);
      })
      ->select('p.name')
      ->groupBy('p.name')
      ->orderBy('p.name')
      ->take(3)
      ->get();

edited Dec 13, 2018 at 10:00

answered Dec 13, 2018 at 9:34

Jerodev

33.3k11 gold badges96 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amol Rokade Over a year ago

Instead of above, can we use this one laravel.com/docs/5.7/queries#conditional-clauses ?

MD. Jubair Mizan · Accepted Answer · 2018-12-13 09:59:22Z

Try this

$query = DB::table('product as p')
  ->join('master as cl', 'p.color', '=', 'cl.name')
  ->join('master as brand', 'p.brand', '=', 'brand.name')
  ->where(function($query) use ($brandname,$_POST){
      $query->where('p.brand', $brandname);
      $query->whereRaw("p.del_flag = 0  and cl.is_active = 1 and brand.is_active = 1 and p.product_type = 1");

      if( isset($_POST['brand'])) {
         $query->where('p.brand', $_POST['brand']);
      }
      if (isset($_POST['color'])) {
         $query->where('p.color', $_POST['color']);
      }
      if (isset($_POST['product_type'])) {
         $query->where('p.product_type', $_POST['product_type']);
      }
  })
  ->select('p.name')
  ->groupBy('p.name')
  ->orderBy('p.name')
  ->take(3);
  ->get();

Hope this will help you

Gaurav Gupta · Accepted Answer · 2018-12-13 09:37:24Z

if you want to add filter in query you can do that by create query object something like that

$result = DB::table('product as p')
      ->join('master as cl', 'p.color', '=', 'cl.name')
      ->join('master as brand', 'p.brand', '=', 'brand.name')
      ->where('p.brand', $brandname)
      ->whereRaw("p.del_flag = 0  and cl.is_active = 1 and brand.is_active = 1 and p.product_type = 1")
      ->select('p.name')
      ->groupBy('p.name')
      ->orderBy('p.name');


if(isset($_POST['brand'])) {
    $result->where('p.brand', $_POST['brand'])
}
if(isset($_POST['color'])) {
    $result->where('p.color', $_POST['color'])
}
if(isset($_POST['product_type'])) {
    $result->where('p.product_type', $_POST['product_type'])
}

$result->take(3)->get();

that will do the trick .

Hưng Trịnh · Accepted Answer · 2018-12-13 10:33:35Z

My last code:

$querydata = DB::table('product as p')
  ->join('master as cl', 'p.color', '=', 'cl.name')
  ->join('master as brand', 'p.brand', '=', 'brand.name')
  ->where('p.brand', $brandname)
  ->whereRaw("p.del_flag = 0  and cl.is_active = 1 and brand.is_active = 1 and p.product_type = 1")
  ->select('p.name')
  ->groupBy('p.name')
  ->orderBy('p.name')
  ->take(3);



  if(!empty($_POST)) {
      unset($_POST['_token']);

  foreach ($_POST as $column => $data) {
    if(empty($data)) {
      continue;
    }

    if(is_array($data)) {

      $querydata = $querydata->where(function($query) use ($column, $data) {
          foreach ($data as $value) {
            $query->orWhere('p.'.$column, $value);
          }
      });
    } else {
      $querydata->where('p.'.$column, $data);
    }

  }


 }

  $result = $querydata->get();

I hope to help somebody.

Collectives™ on Stack Overflow

Laravel Eloquent: Add filter in where (if-else for Conditional Queries)?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related