How can I render search on Livewire with an orWhere clause not related to search?

Question

I have here a Livewire render function that displays records depending on what is searched on the textbox. It was working fine, and giving me results in an autocomplete manner. However, when I added this line ->orWhere('qty_on_hand', '!=', 0), it stops that autocomplete searching.

 <?php
    
    namespace App\Http\Livewire;
    
    use Livewire\Component;
    use App\Models\Product;
    
    class LoadInStockProducts extends Component
    {
        public $searchTerm;
        public $amount = 10;
    
        protected $listeners = [
            'load-more' => 'loadMore'
        ];
       
        public function loadMore()
        {
            $this->amount = $this->amount + 10;
        }
    
        public function render()
        {
            $searchTerm = '%' . $this->searchTerm . '%';
            
            $products = Product::orderBy('name')->where('code', 'like', $searchTerm)
                        ->orWhere('name', 'like', $searchTerm)
                        ->orWhere('description', 'like', $searchTerm)
                        ->orWhere('qty_on_hand', '!=', 0)
                        ->paginate($this->amount);
    
            $this->emit('productStore');
            
            return view('livewire.load-in-stock-products', ['products' => $products]);
        }
    }

How can I make the autocomplete searching work, even with the ->orWhere('qty_on_hand', '!=', 0) condition? Also, another question, I've tried this ->orWhere('srp', '!=', 0.00) and it's not working. How can I make it work for float types? The srp field is a float type by the way.

Any help is much appreciated.

Answer 1

It sounds to me like you want to produce a query where you search for the given fields, and only show the records that has a quantity on hand - that would look like this in raw SQL,

SELECT *
FROM products
WHERE (
      code LIKE '%searchterm%'
      OR name LIKE '%searchterm%'
      OR description LIKE '%searchterm%'
   )
   AND qty_on_hand != 0
ORDER BY name

Note how the search for code/name/description is within its own group, and that you look for any match of either of those and where the quantity is different from zero.

In Laravel, that means you have to group the query as well, using a closure.

$products = Product::where(function($query) use ($searchTerm) {
        return $query->where('code', 'like', $searchTerm)
            ->orWhere('name', 'like', $searchTerm)
            ->orWhere('description', 'like', $searchTerm);
    })
    ->where('qty_on_hand', '!=', 0)
    ->orderBy('name')
    ->paginate($this->amount);

You can introduce more groups if you have other conditions, like having two where() with a closure, where the queries inside use orwhere().

$products = Product::where(function($query) use ($searchTerm) {
        return $query->where('code', 'like', $searchTerm)
            ->orWhere('name', 'like', $searchTerm)
            ->orWhere('description', 'like', $searchTerm);
    })
    ->where(function($query) {
        return $query->where('qty_on_hand', '!=', 0)
            ->orWhere('srp', '!=', 0);
    })
    ->orderBy('name')
    ->paginate($this->amount);