whereHas filter query issue

@webrobert try this but search not working still same issue nothingcan search

  $pi = Product::where('customer_id', $customerId)
            ->where('sys_state', '!=', '-1')
            ->whereHas('supplier', function (Builder $query) use ($search) {
                $query->where('sys_state', '!=', '-1')
                ->orWhere(function ($query) use ($search) {
                    $query->where('name', 'LIKE', '%' . $search . '%')
                    ->where('sys_state','!=','-1');
                });
            })
            ->orWhere(function ($query) use ($search, $customerId) {
                $query->where('prd_description', 'LIKE', '%' . $search . '%')
                ->where('sys_state', '!=', '-1')
                ->where('customer_id',$customerId);
            })
            ->orWhere(function($query) use($search, $customerId){
                $query->where('prd_supplier_item', 'LIKE', '%' . $search . '%')
                ->where('sys_state', '!=', '-1')
                ->where('customer_id',$customerId);
            })
            ->orWhere(function($query) use($search, $customerId){
                $query->where('prd_our_item_no', 'LIKE', '%' . $search . '%')
                ->where('sys_state', '!=', '-1')
                ->where('customer_id',$customerId);
            })
        ->get();

@vandan hang on,

you want products that are related to the customer

that have a supplier that match the search and

that sys_state','!=','-1

does that part work?

$pi = Product::where('customer_id',$customerId)
     ->whereHas('supplier', function (Builder $query) use($search) {
         $query->where('sys_state', '!=', '-1') // this is on both product and on supplier?
               ->orWhere('name','LIKE','%' . $search . '%');
     })
     ->where('sys_state','!=','-1')
     ->get()

@webrobert both customers and suppliers are related to products sys_state','!=','-1 this is just check that this record is delete or active

@vandan ok, does this part work? do you get results?

@webrobert yes but when i search particular product description can't search on it

@vandan something like this...

$pi = Product::where('customer_id',$customerId)
    ->whereHas('supplier', function (Builder $query) use($search) {
     $query->where('sys_state', '!=', '-1') 
           ->orWhere('name','LIKE','%' . $search . '%');
    })
    ->where('sys_state','!=','-1')
    ->where(function ($query) use($search) {
        $query->where('prd_our_item_no', 'LIKE', '%' . $search . '%')
              ->orWhere('prd_supplier_item', 'LIKE', '%' . $search . '%')
              ->orWhere('prd_description','LIKE','%' . $search . '%');
    })
    ->get()

@vandan

Wait

$query
   ->where('sys_state', '!=', '-1')
   ->orWhere('name','LIKE','%' . $search . '%');

If sys_state','!=','-1 is to check whether a record is deleted or not, then that is wrong.

If the record is not deleted, that's it, it will be included regardless of name search. That should be a "and"

$query
   ->where('sys_state', '!=', '-1')
   ->where('name','LIKE','%' . $search . '%');

Perhaps you should include that filter as a global scope in the corresponding eloquent model (whatever model the 'suplier' relation refers to) that way this query will be easier to read and mantain.

@webrobert products and all things work perfect on search but when i search on supplier cant search on with supplier

@psrz when i remove orwhere than all the search not working

@vandan oops

$pi = Product::where('customer_id',$customerId)
     ->where('sys_state','!=','-1')
     ->where(function ($q) use($search) {
         $q->whereHas('supplier', function (Builder $query) use($search) {
             $query->where('sys_state', '!=', '-1')
                   ->where('name','LIKE','%' . $search . '%');
         })
           ->orWhere('prd_our_item_no', 'LIKE', '%' . $search . '%')
           ->orWhere('prd_supplier_item', 'LIKE', '%' . $search . '%')
           ->orWhere('prd_description','LIKE','%' . $search . '%');
     })
     ->get()

@webrobert hy whole search not working but when i remove orWhere

	$q->whereHas('supplier', function (Builder $query) use($search) {
         $query->where('sys_state', '!=', '-1')
               ->where('name','LIKE','%' . $search . '%');
     })

still work but issue is my deleted supplier also display

@vandan

What do you mean the query does not work ? Error ? Not getting the expected results ?

I mean, when you search over the suplier relation: 1) does the record have to be active ? 2) does the record have to match the pattern of name ? If both answers are "yes", then you have to use "and", not "or"

@vandan

Can you show the generated sql query ?

@vandan good catch, I removed the or on the whereHas...

So it works now?

You just need to remove the deleted supplier ?

$q->whereHas('supplier', function (Builder $query) use($search) {
     $query->where('sys_state', '!=', '-1') // <- thats here right
           ->where('name','LIKE','%' . $search . '%');
 })

Do you need more than one state removed?

@webrobert products search works but with supplier name search can't work when i add OrWhere condition & when i remove OrWhere than deleted suppliers show on list

@vandan

what sys_state do you want?

NOT -1

AND more?

@webrobert

That's what I'm refering to.

The deleted suppliers should be filtered. That's a given . But then the name has to match the search. Both things have to be true. That's why can't be "or"

@webrobert yes I want -1 and this is enum type

@psrz yes exactly

@webrobert my system is down whenever I comeback will check but yes thank you so much for guide and help me much appreciate