Laravel Filter Records with empty relationship with pagination

Question

Hello I want to get the records that excludes relationships with empty values. I tried a lot of codes but did not worked out correctly. Here one of my tries:

$product= [];
        $product= Product::select('id','ref_num','eta','customer_id')
        ->when(count($customer_ids) > 0, function($q) use ($customer_ids){
             return $q->whereIn('customer_id',$customer_ids);
        })
        ->when(!is_null($ref_num_q_string) && $ref_num_q_string !=='', function($q) use ($ref_num_q_string){
            return $q->where('ref_num','like','%'.$ref_num_q_string.'%');
        })
        ->with(['customer' => function($q){
            $q->select('id','company_name');
        }])
        // ->whereExists(function($q){
        //     $q->select('id','product_id','total_amount')
        //         ->from('bills')
        //         ->whereColumn('bills.product_id','products.id');
        // })
        // ->whereExists(function($q){
        //     $q->select('id','product_id','total_amount')
        //         ->from('invoices')
        //         ->whereColumn('invoices.product_id','products.id');
        // })
        ->with(['bill' => function($q){
            $q->with(['bill_items']);
            $q->select('id','product_id','total_amount');
        }])
        ->with(['invoice' => function($q){
            $q->with(['invoiceServices']);
            $q->select('id','product_id','total_amount');
        }])
        ->has('invoice')
        ->orHas('bill')
        ->orderBy($sort_by,$sort_type)
        ->paginate(isset($per_page) ? $per_page : 50);

I use the "when" clause during the search filter. If I have 1 "has" method it returns the correct values but when I added "orHas" there goes the issue, it returns the records matching the string but other unnecessary records are with them.

Answer 1

So orHas and orWhere cause some problems for you here. These will cause an issue with the rest of your query, meaning none of your other criteria is considered. You will need to use a closure on your ->has() similar to the following:

->has(function ($query) {
    $query->has('invoice')
        ->orHas('bill');
})
->orderBy($sort_by,$sort_type)
->paginate(isset($per_page) ? $per_page : 50);

Now you will only get results where a row either has an invoice or a bill.

Check out the docs here: https://laravel.com/docs/6.x/queries#parameter-grouping