Couldn't use the whereNotExists clause correctly in Laravel 8 Eloquent-CodePudding

I have 2 tables, one with different users, and the second table is an invoice table called "factures" and has a foreign key of userid, I called it client_id, which I am trying to get is the number of clients created_by a certain administrator and who have no invoices yet, here is what I tried:

$clients = User::select('id')
            ->where([['created_by',$membre_id],['role','Client']])
            ->orWhere([['updated_by',$membre_id],['role','Client']])
            ->whereNotExists(function($query)
              {
                  $query->select(DB::raw('client_id'))
                        ->from('factures')
                        ->where('created_by',$member_id);

              })->get();

but this query gives me all clients created_by $member_id without exception. What is wrong with my query?

CodePudding user response：

Did you try the following:

$clients = User::select('id')
    ->where(function($query) use($member_id){
        $query->where([['created_by',$membre_id],['role','Client']])
            ->orWhere([['updated_by',$membre_id],['role','Client']])
    })
    ->whereNotExists(function($query) use($member_id){
        $query->select(DB::raw('client_id'))
            ->from('factures')
            ->where('created_by',$member_id);
    })
    ->get();
}

This answer applied the OR condition only between the first two conditions (created_by and updated_by) and its result is AND with the third condition.