zaster's avatar

Searching the pivot table

        $billList = Bill::with('company')
         ->where('company_id', $this->payBill->company->id)
         ->where('comp_id', $this->payBill->comp->id)
          ->where('status', 2) //Verified
          ->Where('paid_by', null)
          ->Where('paybill_id', $this->payBill->id)->doesntExist() //Need to find this from bill_paybill table
          ->when($this->search, function($query){
            return $query->where(function($query){
                $query->where('name', 'like', '%'. $this->search . '%');
            });
        })
        ->orderBy('id', 'ASC')
        ->paginate(10);

The above query searches the bills table

How can i use this below query on the pivot table and have all in one query

->Where('paybill_id', $this->payBill->id)->doesntExist()

Bill Model

    public function payBills()
    {
        return $this->belongsToMany('App\Models\PayBill', 'bill_paybill', 'bill_id', 'paybill_id');
    }

PayBill Model

    public function bills()
    {
        return $this->belongsToMany('App\Models\Bill', 'bill_paybill', 'paybill_id', 'bill_id');
    }
0 likes
3 replies
rodrigo.pedra's avatar
Level 56

Try using ->whereDoesntHave()

$billList = Bill::with('company');
    ->where('company_id', $this->payBill->company->id)
    ->where('comp_id', $this->payBill->comp->id)
    ->where('status', 2) //Verified
    ->whereNull('paid_by')
    ->whereDoesntHave('payBills') // <<< TRY THIS
    ->when($this->search, function ($query) {
        $query->where('name', 'like', '%' . $this->search . '%');
    })
    ->orderBy('id')
    ->paginate(10);

reference: https://laravel.com/docs/9.x/eloquent-relationships#querying-relationship-absence

Please or to participate in this conversation.