I am collecting all the data like id, name, date, empid from the controller in variable data

$data = $request->all();

now i want to select all the rows from the mysql table base on the input value i receive from request like this.

if no value is given then select all from DB

if ($data['keyvalue'] == '' && $data['datefrom'] =='' && $data['dateto'] =='' && $data['prepid'] =='' ){

           $country =  country::get(); 

if only certain input value is given

elseif ($data['keyvalue'] !== '' && $data['datefrom'] =='' && $data['dateto'] =='' && $data['prepid'] =='' ){

            $country = country::where('sr_status','LIKE','%'.$data['status'].'%')
                    ->Where('sr_name','LIKE','%'.$data['keyvalue'].'%')
                    ->orWhere('tel_code','LIKE','%'.$data['keyvalue'].'%')
                    ->orWhere('country_code','LIKE','%'.$data['keyvalue'].'%')
                    ->orWhere('currency_name','LIKE','%'.$data['keyvalue'].'%')
                    ->orderBy('sr_id')
                    ->get();

}elseif ($data['keyvalue'] == '' && $data['datefrom'] !=='' && $data['dateto'] =='' && $data['prepid'] =='' ){

            $country = country::where('sr_status','LIKE','%'.$data['status'].'%')
                    ->whereDate('created_at','>=',$data['datefrom'])
                    ->orderBy('sr_id')
                    ->get();

}elseif ($data['keyvalue'] == '' && $data['datefrom'] =='' && $data['dateto'] !=='' && $data['prepid'] =='' ){

            $country = country::where('sr_status','LIKE','%'.$data['status'].'%')
                    ->whereDate('created_at','<=',$data['dateto'])
                    ->orderBy('sr_id')
                    ->get();  

}elseif ($data['keyvalue'] == '' && $data['datefrom'] =='' && $data['dateto'] =='' && $data['prepid'] !=='' ){

            $country = country::where('sr_status','LIKE','%'.$data['status'].'%')
                    ->Where('prep_emp','LIKE','%'.$data['prepid'].'%')
                    ->orderBy('sr_id')
                    ->get();   
                    
}

and if all input value is given

elseif ($data['keyvalue'] !== '' && $data['datefrom'] !=='' && $data['dateto'] !=='' && $data['prepid'] !=='' ){ 

           $country = country::where('sr_status','LIKE','%'.$data['status'].'%')
                    ->whereDate('created_at','>=',$data['datefrom'])
                    ->whereDate('created_at','<=',$data['dateto'])
                    ->Where('sr_name','LIKE','%'.$data['keyvalue'].'%')
                    ->orWhere('tel_code','LIKE','%'.$data['keyvalue'].'%')
                    ->orWhere('country_code','LIKE','%'.$data['keyvalue'].'%')
                    ->orWhere('currency_name','LIKE','%'.$data['keyvalue'].'%')
                    ->Where('prep_emp','LIKE','%'.$data['prepid'].'%')
                    ->orderBy('sr_id')
                    ->get();
}

everything working fine i just want to simplify this code..

CodePudding user response：

You can use when() method which is conditional.

https://laravel.com/docs/8.x/queries#conditional-clauses

$countries = Country::when($request->filled('status'), function ($query) use ($request) {
    $query->where('sr_status', 'LIKE', "%{$request->input('status')}%");
})->when($request->filled('datefrom'), function ($query) use ($request) {
    $query->whereDate('created_at', '>=', $request->input('datefrom'));
})->when($request->filled('dateto'), function ($query) use ($request) {
    $query->whereDate('created_at', '<=', $request->input('dateto'));
})->when($request->filled('keyvalue'), function ($query) use ($request) {
    $query->Where('sr_name', 'LIKE', "%{$request->input('keyvalue')}%")
        ->orWhere('tel_code', 'LIKE', "%{$request->input('keyvalue')}%")
        ->orWhere('country_code', 'LIKE', "%{$request->input('keyvalue')}%")
        ->orWhere('currency_name', 'LIKE', "%{$request->input('keyvalue')}%");
})->when($request->filled('prepid'), function ($query) use ($request) {
    $query->where('prep_emp', 'LIKE', "%{$request->input('prepid')}%");
})->orderBy('sr_id')->get();

CodePudding user response：

You could do it like this:

$country = null;

if ($value0 !== null) 
{
    $country = $country ?? Country::where('query0 here');
}
else if ($value1 !== null)
{
    $country = $country ?? Country::where('query1 here');
}
...
// end of your else ifs
// you do not need to check if all of the value exist because
// the query when all of the value exist and the partial value
// exist combined is just the same

$country = $country ?? $country->get();