The issue of not using whereNotExists clause correctly in Laravel 8 Eloquent remains unresolved

Question

I have two tables, one is a table with different users and the other is an invoice table called "factures" which has a foreign key userid which I call client_id. What I want to get is the number of customers that were created by a certain admin and don't have an invoice yet. This is the code 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 returns me all customers created by $member_id, no exceptions. Is there anything wrong with my query?

Answer 1

Have you tried 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 only applies the OR condition between the first and second conditions (created_by and updated_by) and its result is combined with the AND of the third condition .