Summer Sale! All accounts are 50% off this week.

Ligonsker's avatar

Problem with filtering query

I have a table with search box and a checkbox button for adult users (age above 18). The users have a name and an email. The issue is when the adult checkbox is checked and you search by an email, it doesn't return any result.

For example the following two rows in the database:

Name                     Age	    Email
Concepcion Turcotte      16	   [email protected]
Emmy Hoeger              67	   [email protected]

and I Search for "kozey." it returns the two rows above. But if I check the adult checkbox and search for "kozey.", I get no results. This is the query:

        $is_adult = $request->input('adult');
        $search = $request->input('search');

        $users = User::when($is_adult, function ($query, $search) {
            $query->where('name', 'like', "%$search%")->orWhere('email', 'like', "%$search%")->where('age', '>', 18);
        })->paginate(15)->withQueryString();
0 likes
4 replies
Sinnbeck's avatar

The when should be seperate

$users = User::where('name', 'like', "%$search%")
->when($is_adult, function ($query) {
            $query->where('age', '>', 18);
        })
->paginate(15)
->withQueryString();

And shouldn't 18 be included? >=

2 likes
Ligonsker's avatar

@Sinnbeck thanks .

I think I did a mistake then by writing a shorter version of the actual query because I thought it would be quicker to read, but this is the full query:

$users = User::when($search, function ($query, $search) {
        $query->where('name', 'like', "%$search%")->orWhere('email', 'like', "%$search%");
    })->when($is_adult, function ($query, $search) {
            $query->where('name', 'like', "%$search%")->orWhere('email', 'like', "%$search%")->where('age', '>', 18);
        })->paginate(15)->withQueryString();

So should I actually change the above to:

        $query->where('name', 'like', "%$search%")->orWhere('email', 'like', "%$search%");
    })->when($is_adult, function ($query, $search) {
            $query->where('age', '>', 18);
        })->paginate(15)->withQueryString();

?

1 like
Sinnbeck's avatar

@Ligonsker you need to wrap the two first in (). You do do with a callback

$query->where(function ($query) use ($search) {
    $query->where('name', 'like', "%$search%")->orWhere('email', 'like', "%$search%");
    });
})->when($is_adult, function ($query, $search) {
            $query->where('age', '>', 18);
        })->paginate(15)->withQueryString();
2 likes

Please or to participate in this conversation.