Laravel Query Builder Select with empty Input

Question

I'm building up a filterable list with Laravel 5.0 and I'm crashing on following Problem.

I get the filter parameters from a HTML form and pass them to the Query Builder, but if a form input stays empty all rows of the table filtered by the other filters should be returned.

Example code:

$collection = Model::withTrashed()
    ->where('attr1', 'LIKE', \Request::input('attr1', '%'))
    ->where('attr2', 'LIKE', \Request::input('attr2', '%'))
    ->where('attr3', 'LIKE', \Request::input('attr3', '%'))
    ->get();

This seems to be most correct code for me, but it doesn't work as expected. Do you know a good solution for my Problem? I don't want to integrate a messy switch/case statement for proofing for existence and building the collection up manually. :(

Thanks for your help.

mpartarrieu · Accepted Answer · 2015-08-05 08:43:08Z

2

It seems that your problem is that you are always passing the filters to the query and you should only pass them if they are not empty.

Maybe somethig like this would work for you:

$results = Model::withTrashed();
if (\Request::input('attr1')) {
    $results->where('attr1', 'LIKE', \Request::input('attr1', '%'));
}
if (\Request::input('attr2')) {
    $results->where('attr2', 'LIKE', \Request::input('attr2', '%'))
}
if (\Request::input('attr3')) {
    $results->where('attr3', 'LIKE', \Request::input('attr3', '%'))
}
$collection = $results->get();

answered Aug 5, 2015 at 8:43

mpartarrieu

563 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4374647 Over a year ago

I guess I have to use it this way. :) Thanks!

lowerends · Accepted Answer · 2015-08-05 08:42:56Z

1

You need to tweak your query a bit like this:

$collection = Model::withTrashed() ->where('attr1', 'LIKE', '%' . \Request::input('attr1') . '%') ->where('attr2', 'LIKE', '%' . \Request::input('attr2') . '%') ->where('attr3', 'LIKE', '%' . \Request::input('attr3') . '%') ->get();

This will make sure that the LIKE clause is still correct when an empty input parameter is sent.

answered Aug 5, 2015 at 8:42

lowerends

5,2752 gold badges27 silver badges41 bronze badges

5 Comments

user4374647 Over a year ago

But doesn't it returns model with attr1 == 11, when I enter 1 in attr1 input?

lowerends Over a year ago

Yes, it would, as that is the goal of using LIKE. If you don't want that, then you shouldn't use LIKE but rather where('attr1', \Request::input('attr1')). In that case you need an if-statement to check for existence of attr1 in the request.

user4374647 Over a year ago

But then it returns only rows with attr1 == empty, if nothing was selected in attr1 input. :D

lowerends Over a year ago

Indeed, that's why I mentioned that you need to check for existence of the attr1 parameter in the input.

user4374647 Over a year ago

Yeah, I guess you're right. Thanks for you help! :)

Collectives™ on Stack Overflow

Laravel Query Builder Select with empty Input

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related