Query model based on variable input that can be null?

I am trying to search my products table with the following form. I want people to have the option to be able to just search by 1 filter or all filter options.

The issue I am running into is that then sometimes field values can/will be null OR equal to 'any'.

So how can I run a query where if 'any' is selected, it will return all results for that filter, and if null is the value it will do the same?

My current code below.

Form

<form method="GET" action="/">
    <input type="text" name="search_location">
    <input type="text" name="search_keywords">
    <select class="wide" name="color">
        <option value="any">Any Color</option>  
        <option value="red">Red</option>
        <option value="blue">Blue</option>
        <option value="green">Green</option>
    </select>
    <select class="wide" name="price">
        <option value="any">Any Price</option>  
        <option value="10"></option>
        <option value="20"></option>
        <option value="30"></option>
    </select>
</form>

Controller

public function search(Request $request)
{
    $location = $request->search_location;
    $keywords = $request->search_keywords;
    $color = $request->color;
    $price = $request->price;
    
    $products = Product::where('location', $location)
                 ->where('keywords', $keywords)
                 ->where('color', $color)
                 ->where('price', $price)
                 ->get();       
    
    dd($products);
}

36864

7 years ago

Level 13

You could use a conditional clause to decide whether or not you want to apply a filter: https://laravel.com/docs/5.6/queries#conditional-clauses

So, something like

    Product::when($color !== 'any', function($query, $color) {
        return $query->where('color', $color);
    })
    ->(...)
    ->get()

jpeterson579

7 years ago

Level 7

@36864 Thank you for the response. So this could work but I have 2 questions.

$color will only return true or false inside the when function so how do i get the actual value from the form.

Product::when($color !== 'any', function($query, $color) {
    dd($color);  // Returns true - doesnt say red or blue etc...
    return $query->where('color', $color);
})
->(...)
->get()

Will this work with multiple when statement and go from one to the next to the next?

Cronix

7 years ago

Best Answer

Level 67

You can also just dynamically build up the query

$products = Product::where('location', $location)
    ->where('keywords', $keywords)
    ->where('price', $price);

if ($color != 'any') {
    $products->where('color', $color);
}

$products = $products->get()

1 like

36864

7 years ago

Level 13

Sorry, I made an error in my example. This should work though:

 Product::when($color !== 'any', function($query) use($color) {
        return $query->where('color', $color);
    })
    ->(...)
    ->get();

Please or to participate in this conversation.