phpmysqllaravel

List filtering of users, returns empty object

I have two list-filtering inputs, if used individually they work perfectly but when used together it returns an empty object.

the filters contain one simple search Input field, and one select.

they trigger an API call to the server which when used both looks like this

http://127.0.0.1:8000/api/user?&search=jom&type=admin

the method which this call trigger looks like this

public function index(Request $request) {
        $search = $request->input('search');
        $type = $request->input('type');

        $user = User::select('*');

        $this->checkSearch($user, $search); // check for search
        $this->filterUserType($user, $type); // filter user type
...

the method checkSearch looks like this

    private function checkSearch(&$query, $search) {

        if (!isset($query)) {
            return $query;
        }

        if (!is_null($search)) {
            $searchTerms = $this->stringToArray($search, ' ');

            $query = $query->where(function ($query) use ($searchTerms) {
                for ($i = 0, $max = count($searchTerms); $i < $max; $i++) {
                    $term = str_replace('_', '\_', mb_strtolower('%' . $searchTerms[$i] . '%'));
                    $query->whereRaw("(Lower(name) LIKE ?)", [$term, $term])
                        ->orWhereRaw("(Lower(bio) LIKE ?)", [$term, $term]);
                }
            });
        }
    }

and the filterUserType like this

    private function filterUserType(&$query, $type) {
        if (!isset($query)) { return $query; }

        if (!is_null($type)) {
            $query = $query->where( 'type', $type);
        }
    }

I've tried to check the where on the filterUserType method, to orWhere but this just returns values of both not combined.

Solution

  • I triggered a break on the raw query and it appeared like this

    select *  from `users` where ((Lower(name) LIKE %jom%) or (Lower(bio) LIKE %jom%)) and `type` = %jom%)

    When I switched the methods I got the right results. Switching from

     $this->checkSearch($user, $search); // check for search
 $this->filterUserType($user, $type); // filter user type

    to

     $this->filterUserType($user, $type); // filter user type
 $this->checkSearch($user, $search); // check for search

    Still don't know why but it worked