What will the best solution for this multiple optional filter?

Question

These are all optional fields, so will I have to write multiple queries with conditions or is there any way to handle this using Laravel? What will be the query looks like?

Thanks

Answer 1

It depends a bit on how the filters are submitted, but you can do one of the following two things (and probably a gazillion more...):

public function listCars(Request $request)
{
    $cars = Car::when($request->get('make'), function ($query, $make) {
            $query->where('make', $make);
        })
        ->when($request->get('model'), function ($query, $model) {
            $query->where('model', $model);
        })
        ->...
        ->get();

    // do what you have to do
}

So you are basically wrapping your query builder calls in when($value, $callback), which will only execute $callback if $value evaluates to true. When you retrieve a not set parameter with $request->get('parameter'), it will return null and the callback is not executed. But be careful, if $value is 0 it will also not execute the callback. So be sure you don't have this as an index.

As alternative to this, you can also do the same thing but with a bit less eloquent expressions...

public function listCars(Request $request)
{
    $query = Car::query();

    if($request->filled('make')) {
        $query->where('make', $request->get('make'));
    }
    if($request->filled('model')) {
        $query->where('model', $request->get('model'));
    }

    // some more filtering, sorting, ... here

    $cars = $query->get();

    // do what you have to do
}

Answer 2

Here is a working example of something similar query i have in my app.

   $filters = $vehicle->newQuery();

    if (!empty($request->make)) {
        $filters->where('make_id', $request->make);

    }
    if (!empty($request->carmodel)) {
        $filters->where('carmodel_di', $request->carmodel);

    }
    if (!empty($request->year)) {
        $filters->where('year_id', $request->year);

    }
    if (!empty($request->engine)) {
        $filters->where('engine_id', $request->engine);

    }

    if (!empty($request->price)) {
        $filters->where('price_id', $request->price);

    }

    $cars = $filters->latest()->paginate(50);

and now push the $cars variable to view. I hope this works for you or atleast gives you an idea on how to proceed

Answer 3

here is a simple way, you can also make the joins conditional inside the ->when() condition, if you are in Laravel version > 5.4, use $request>filled() instead of $request->has()

    public function listCars(Request $request)
{
    $cars = Car::when($request->has('make'), function ($query)use($request) {
            $query->join('maker','car.makerId','=','maker.id')
            ->where('make', $request->input('make'));
        })
        ->when($request->has('model'), function ($query)use($request) {
            $query->where('model', $request->input('model'));
        })
        ->...
        ->get();

    // you can even make the join conditionaly, 
}

Answer 4

$fiterItem = ['make','model','year','engine','price'];
$filters = $vehicle->newQuery();

foreach ($filter as $item) {
    if ($r->filled($item)) {
      $list->where($item, $r->query($item));
    }
}
$list = $filters->paginate(20);