Issue with implementing a search functionality with Laravel

Question

I am trying to implement a search feature in a site. A user can specify a search string that could match a product_id, product_name or the product_sku. What I want to implement is a search function that would return all that matches.

What I've come with so far:

 return Product::with(['getProductItem' => fn ($query) => $query->orWhere('itemSkuNumber', '=', $request->search)])
                ->where(
                    'name',
                    $request->has('match') ? '=' : 'like',
                    $request->has('match') ? $request->search : '%' . $request->search . '%'
                )
                ->orWhere('id', '=', $request->search)->get();

This has worked if I specify a product_id or product_name but does not return anything if I provide an sku.

Answer 1

You should wrap orWheres with another where because it will try to filter all of them.

        Product::with(['getProductItem'])
        ->whereHas('getProductItem', function ($query) use ($request) {
            $query->where(function ($subquery) use ($request) {
                $subquery->where('itemSkuNumber', '=', $request->search);
                //put your orwhere queries in here
            });
        })
        ->get();