How to Have String Querry And Date Querry at The Same Time in Laravel with Elloquent-CodePudding

At First My Code Work Fine with just one parameter when querrying,but when i add more parameter it start to error... Here is MyEditController File:

class AdminEditController extends Controller
{
//edit didalam lihat_datafile
public function edit(Request $request,$id){
 $data = lokasipekerjaan::find($id);
$data->mid=$request->mid;
$data->subid=$request->subid;
$data->pekerjaan=$request->pekerjaan;
$data->lokasi=$request->lokasi;
$data->uom=$request->uom;
$data->cut=$request->cut;
$data->fill=$request->fill;
$data->area=$request->area;
$data->tanggal=$request->tanggal;

$data->save();
return redirect()->back();

}

Here is My web.php :

Route::resource('lokasipekerjaan', AdminEditController::class);

Here is My Blade:

 @foreach($data as $datas)
                        <form action="{{route('lokasipekerjaan.edit',[$datas->id])}}" method="PUT">
                      <tr >
                        <td> <input  type="text" value="{{ $datas->id }}"  style="background-color: #E0FFFF;width:40px;height:40px;" name="id" :value="old('id')"> </input> </td>
                        <td><input id="{{ $datas->mid }}" type="text" value="{{ $datas->mid }}"  style="background-color:   #E0FFFF;width:40px;height:40px;" name="mid" :value="old('mid')"></td>
                        <td> <input id="{{ $datas->subid }}" type="text" value="{{ $datas->subid }} "  style="background-color: #E0FFFF;width:100px;height:40px;" name="subid" :value="old('subid')"> </td>
                        <td> <input id="{{ $datas->pekerjaan }}" type="text" value="{{ $datas->pekerjaan }} " style="background-color:  #E0FFFF;height:40px;" name="pekerjaan" :value="old('pekerjaan')"> </td>
                        <td> <input id="{{ $datas->lokasi }}" type="text" value="{{ $datas->lokasi }} "  style="background-color:   #E0FFFF;height:40px;" name="lokasi" :value="old('lokasi')"> </td>
                        <td> <input id="{{ $datas->cut }}" type="text" value="{{ $datas->cut }} "  style="background-color: #E0FFFF;height:40px;" name="volume" :value="old('cut')"> </td>
                        <td> <input id="{{ $datas->fill }}" type="text" value="{{ $datas->fill }} "  style="background-color:   #E0FFFF;height:40px;" name="volume" :value="old('fill')"> </td>
                        <td> <input id="{{ $datas->area }}" type="text" value="{{ $datas->area }} "  style="background-color:   #E0FFFF;height:40px;" name="volume" :value="old('area')"> </td>
                        <td> <input id="{{ $datas->uom }}" type="text" value="{{ $datas->uom }} "  style="background-color: #E0FFFF;width:40px;height:40px;" name="uom" :value="old('uom')"> </td>
                        <td>  <input value="{{ $datas->tanggal }} " type="text" value="{{ $datas->tanggal }} "  style="background-color:    #E0FFFF;width:100px;height:40px;" name="tanggal" :value="old('tanggal')"> </td>
                        <td>
                            <input type="hidden" name="_method" value="PUT">
                            <input type="hidden" name="_token" value="{{csrf_token()}}">
                            <input type="submit" onclick="return confirm('apakah anda yakin ingin mengedit data ini ?');"  value="Edit"/>@method('PUT')
                          </form></td>

Here is My Controller:

public function lihatdata(){
        if(Auth::id()){
        $caridata = $_GET['querry'];
        $tanggal_dari = $_GET['tanggaldari'];
        $tanggal_ke = $_GET['tanggalke'];
        $filtersubid = dataacuan::select('subid')->whereNotNull('subid')->distinct()->get();
        $data = lokasipekerjaan::when($caridata,function($caridatat,$caridata){
            return $caridatat->where('cut','LIKE','%'.$caridata.'%')
            ->orwhere('fill','LIKE','%'.$caridata.'%')
            ->orwhere('mid','LIKE','%'.$caridata.'%')
            ->orwhere('subid','LIKE','%'.$caridata.'%')
            ->orwhere('lokasi','LIKE','%'.$caridata.'%')
            ->orwhere('id','LIKE','%'.$caridata.'%')
            ->orwhere('pekerjaan','LIKE','%'.$caridata.'%')
            ->groupby('subid')->select(
                DB::raw("DATE(tanggal) as tanggal"),
            );
        })->whereDate('tanggal','>=',$tanggal_dari) 
        ->whereDate('tanggal','<=',$tanggal_ke)->paginate(5);
        return view('admin.lihat_data',compact('data','caridata','filtersubid','tanggal_dari','tanggal_ke'));}else{
            return view('admin.notfound');
        }
    }

When I adding Some Parameter using this code it start showing error:

->select(
            DB::raw("DATE(tanggal) as tanggal"),
        );
    })->whereDate('tanggal','>=',$tanggal_dari) 
    ->whereDate('tanggal','<=',$tanggal_ke)

When i filter it with date it work fine but when i start to querrying with strings it showing this error below:

How Can i fix that please ?

Thank You....

CodePudding user response：

The parameter needs to be named:

{{route('lokasipekerjaan.edit',['lokasipekerjaan'=>$datas->id])}}

Also make sure you're getting matches. $datas->id is probably null. When paginating you can check $data->total() if it's 0 that means there's no matches when you're filtering by a keyword.

Edit: So it looks like your when logic overrides the select logic:

->select(
    DB::raw("DATE(tanggal) as tanggal"),
)

In the above, when you before were getting *, you now no longer get the id field. You should add the fields which you need:

->select(
    DB::raw("DATE(tanggal) as tanggal, id")
)

Edit2:

Regarding the outside date range, there's no parenthesis within the query that will get generated. You'd want to wrap the searching logic within parenthesis otherwise the OR's would check whichever is true, and short circuit before the date range check.

Instead of this:

$data = lokasipekerjaan::when($caridata,function($caridatat,$caridata){
            return $caridatat->where('cut','LIKE','%'.$caridata.'%')
            ->orwhere('fill','LIKE','%'.$caridata.'%')
            ->orwhere('mid','LIKE','%'.$caridata.'%')
            ->orwhere('subid','LIKE','%'.$caridata.'%')
            ->orwhere('lokasi','LIKE','%'.$caridata.'%')
            ->orwhere('id','LIKE','%'.$caridata.'%')
            ->orwhere('pekerjaan','LIKE','%'.$caridata.'%')
            ->groupby('subid')->select(
                DB::raw("DATE(tanggal) as tanggal"),
            );
        })->whereDate('tanggal','>=',$tanggal_dari) 
        ->whereDate('tanggal','<=',$tanggal_ke)->paginate(5);

Wrap the search within a where like so:

        $data = lokasipekerjaan::when($caridata,function($caridatat,$caridata){
            return $caridatat->where(function($caridatat) use ($caridata) {
                $caridatat->where('cut','LIKE','%'.$caridata.'%')
                ->orwhere('fill','LIKE','%'.$caridata.'%')
                ->orwhere('mid','LIKE','%'.$caridata.'%')
                ->orwhere('subid','LIKE','%'.$caridata.'%')
                ->orwhere('lokasi','LIKE','%'.$caridata.'%')
                ->orwhere('id','LIKE','%'.$caridata.'%')
                ->orwhere('pekerjaan','LIKE','%'.$caridata.'%')
                ->groupby('subid')->select(
                    DB::raw("DATE(tanggal) as tanggal"),
                );
            });
        })->whereDate('tanggal','>=',$tanggal_dari) 
        ->whereDate('tanggal','<=',$tanggal_ke)->paginate(5);

CodePudding user response：

The route lokasipekerjaan/{path_parameter}/edit is missing a required path parameter. Look for code snippets like

route('lokasipekerjaan.edit', $params)

on admin.lihat_data

view file. the $params variable may by empty or null?

CodePudding user response：

i think i get the answer this is when the code error:

public function lihatdata(){
    if(Auth::id()){
    $caridata = $_GET['querry'];
    $tanggal_dari = $_GET['tanggaldari'];
    $tanggal_ke = $_GET['tanggalke'];
    $filtersubid = dataacuan::select('subid')->whereNotNull('subid')->distinct()->get();
    $data = lokasipekerjaan::when($caridata,function($caridatat,$caridata){
        return $caridatat->where('cut','LIKE','%'.$caridata.'%')
        ->orwhere('fill','LIKE','%'.$caridata.'%')
        ->orwhere('mid','LIKE','%'.$caridata.'%')
        ->orwhere('subid','LIKE','%'.$caridata.'%')
        ->orwhere('lokasi','LIKE','%'.$caridata.'%')
        ->orwhere('id','LIKE','%'.$caridata.'%')
        ->orwhere('pekerjaan','LIKE','%'.$caridata.'%')
        ->groupby('subid')->select(
            DB::raw("DATE(tanggal) as tanggal"),
        );
    })->whereDate('tanggal','>=',$tanggal_dari) 
    ->whereDate('tanggal','<=',$tanggal_ke)->paginate(5);
    return view('admin.lihat_data',compact('data','caridata','filtersubid','tanggal_dari','tanggal_ke'));}else{
        return view('admin.notfound');
    }
}

this when it work:

 public function lihatdata(){
    if(Auth::id()){
    $caridata = $_GET['querry'];
    $tanggal_dari = $_GET['tanggaldari'];
    $tanggal_ke = $_GET['tanggalke'];
    $filtersubid = dataacuan::select('subid')->whereNotNull('subid')->distinct()->get();
    $data = lokasipekerjaan::when($caridata,function($caridatat,$caridata){
        return $caridatat->where('cut','LIKE','%'.$caridata.'%')
        ->orwhere('fill','LIKE','%'.$caridata.'%')
        ->orwhere('mid','LIKE','%'.$caridata.'%')
        ->orwhere('subid','LIKE','%'.$caridata.'%')
        ->orwhere('lokasi','LIKE','%'.$caridata.'%')
        ->orwhere('id','LIKE','%'.$caridata.'%')
        ->orwhere('pekerjaan','LIKE','%'.$caridata.'%')
        ->groupby('subid');
    })->whereDate('tanggal','>=',$tanggal_dari) 
    ->whereDate('tanggal','<=',$tanggal_ke)->paginate(5);
    return view('admin.lihat_data',compact('data','caridata','filtersubid','tanggal_dari','tanggal_ke'));}else{
        return view('admin.notfound');
    }
}

this is what i remove in my code :

->select(
        DB::raw("DATE(tanggal) as tanggal"),
    )

i don't know how my code is working... but if someone know the theory behind that you guys can answer it....

but it doesn't work really what i wanted to... it's still showing the other data outside of the date.if anyone know how to filter it entirely and ignore the querry behind the date parameter please ?

thank you for the help guys...