Mysql – Apply multiple filters to a table in Laravel 5

eloquentlaravellaravel-5.2MySQL

I have a vehicles table. In my vehicles search page, I filter the vehicles according to models, colors etc selected.

In my code I tried to instantiate the vehicle model. Then applied the filters to it and tried to run query using get(), but feeds me with errors.

Code here:

    $vehicles = Vehicle::all();
    if($request->input('model'))
    {
        $vehicles->whereHas('model', function($q)
        {
            $q->where('model_name', '=', 'CL');

        });

    }
    if($request->input('color'))
    {
        $vehicles->where('color', '=', 'red');
    }
    // other filters here
    $result = $vehicles->get();
    dd($result);

Here a single filter works if I directly use whereHas on the Vehicle model like Vehicle::whereHas(). What is the solution if I had to apply multiple filters based on conditions.
Who saves my day 🙂

Best Answer

So all you would need to do essentially is to have a $conditions array that has your filters, such as:

$conditions = collect([]);
$conditions->push($request->only(['model_name', 'color', 'year', 'valueMin', 'valueMax']'));

This will make a collection that contains only the key => value pairs from your model, color, year, valueMin and valueMax properties (maybe from some form?)

Then you'd simply pass that collection over to your query, like so:

Vehicle::where(function($q) use ($conditions){
    if ($conditions->has('color')) {
        $q->where('color', $conditions->get('color'));
    }

    return $q;
})->whereHas('model', function($q) use ($conditions) {
    return $q->where($conditions->toArray());
})->get();

This way you don't need to worry about hard coding your conditions in your query and you can have a never ending limit. You simply need to pass in an array with key value pairs when making direct comparisons.

This solution will allow for the dynamic property comparisons that you're looking for.

Related Topic