Filtering data by drop down in Laravel-CodePudding

This is my first attempt at Laravel and I got stuck trying to filter my data according to my drop down list.

I have an index page where data for a vehicle log sheet is captured but I cant seem to figure out the MYSQL to filter it based on the dropdown item I pick. If the user picks a staff name from the dropdown and click the filter button, it should show all the records for that staff member.

my controller:

 public function vehiclereport()
    {

        // $energy = Maintenance::orderBy('id', 'desc')->paginate(5);
        $energy = VehicleLog::join('vehicle', 'vehicleslog.vehicle_id', '=', 'vehicle.id')
            ->join('staff', 'vehicleslog.staff_key', '=', 'staff.smsstaff_key')
            ->get();

        $cars = Vehicle::get();
        $staff = Staff::all();
        
        return view('admin.vehiclereport', compact('energy', 'cars', 'staff'));
    }

Has anyone done this, or know a way?

CodePudding user response：

Here's the most simplistic solution to get you started:

 public function vehiclereport()
    {

        $energy = VehicleLog::join('vehicle', 'vehicleslog.vehicle_id', '=', 'vehicle.id')
            ->join('staff', 'vehicleslog.staff_key', '=', 'staff.smsstaff_key')
            ->when(request()->input('staff_id', function ($query, $staffId) {
                   $query->where('staff.id', $staffId)
             })
            ->get();

        $cars = Vehicle::get();
        $staff = Staff::all();
        
        return view('admin.vehiclereport', compact('energy', 'cars', 'staff'));
    }

Then you need to modify your view to add:

<form>
<select name="staff_id" id="staff_id">
<option></option>
@foreach ($staff as $staffMember) 
    <option value="{{$staffMember->id}}" {{request()->input('staff_id') === $staffMember->id ? 'selected="selected"' : ''}}>$staffMember->name</option>
@endforeach
</select>
<button>Filter by selected staff member</button>
</form>

This should be the most basic example which does a full page refresh and filters the list by the option you selected.

If you want something more complicated I suggest reading more tutorials by good providers like e.g. Laracasts

CodePudding user response：

Try with leftjoin

Replace code as

 public function vehiclereport()
    {

        // $energy = Maintenance::orderBy('id', 'desc')->paginate(5);
        $energy = VehicleLog::leftJoin('vehicle', 'vehicleslog.vehicle_id', '=', 'vehicle.id')
            ->leftJoin('staff', 'vehicleslog.staff_key', '=', 'staff.smsstaff_key')
            ->get();

        $cars = Vehicle::get();
        $staff = Staff::all();
        
        return view('admin.vehiclereport', compact('energy', 'cars', 'staff'));
    }