Add "where" and "whereIn" clause only when filter value is not null-CodePudding

I am looking for optimum way to add where clause in Laravel query when there are multiple filters(5 filters) only if filter values are not null.

Right now I am having 6 filters on report on front end which will send input to api.

below is a pseudocode which i want to achive.

$per_page = 20;
//Get input from request
$input1 = $request->input1;
$input2 = $request->input2;
$start_date = $request->start_date;
//if process is null
if($start_date != ''){
    $start_date = $start_date . ' 00:00:00';
}
$end_date = $request->end_date;
if($end_date != ''){
    $end_date = $end_date . ' 00:00:00';
}
$process = $request->process;

//if process is null
if($process == '')
{
    $process = [1,2,3,4];
}
$status = $request->status;
$assigned_user = $request->user_id;

//Get data: I want to achieve something like this

$items = DB::table('transactions_tbl')->select([fields_array])
         ->leftJoin('tbl_1 as table1','table1.id','=','transactions_tbl.tbl1_f_key')
         ->leftJoin('tbl_2 as table2','table2.id','=','transactions_tbl.tbl2_f_key')
         
         //Add where only if $input 1 is not null
         ->where('transactions_tbl.input1',$input1)
         //Add where if $request->process is not null else add whereIn
         if($request->process != ''){
          ->where('transactions_tbl.process',$process)
         }else{
          ->whereIn('transactions_tbl.process',$process)
         }
         // Other conditions based on input 
         ......
         //get paginate data
         ->paginate($per_page);

I know the simple solution using combination of multiple if else conditions. but i want to know if there is any other optimum way to do the solution.

CodePudding user response：

use when() method

Here is the definition

public function when($value, callable $callback = null, callable $default = null)

I refactored your code a little

$items = DB::table('transactions_tbl')->select([fields_array])
    ->leftJoin('tbl_1 as table1', 'table1.id', '=', 'transactions_tbl.tbl1_f_key')
    ->leftJoin('tbl_2 as table2', 'table2.id', '=', 'transactions_tbl.tbl2_f_key')
    ->when(filled($input1), function ($query) use ($input1) {
        $query->where('transactions_tbl.input1', $input1);
    })->when($request->isNotFilled('process'), function ($query) use ($process) {
        $query->whereIn('transactions_tbl.process', $process);

    }, function ($query) use ($process) {
        $query->where('transactions_tbl.process', $process);
    })->paginate($request->input('per_page'), 20);