Only where nested relation exists?

Hi all,

Apologies if this has already been asked/covered (I promise I did look before posting!).

I have 4 models, Clients, Projects, Budgets and Timeentries.

Each Client can have multiple Projects, each project can have multiple Budgets and each Project can have multiple Timeentries logged against them.

I the lookup needs the following criteria

1. Only Client records that have Projects
2. And only where Projects have Budgets assigned to them
3. And then work out from the Timeentries how much of the Budget has been used.

I've kind of achieved the above but Projects are being pulled out regardless of whether they have budgets or not. The aim is to produce a daily notification of projects that are approaching or have exhausted their budget.

Models below (I inherited the database so I'm taking no blame for the column naming conventions used).

app\Models\Clients

class Clients extends Model
{

    public function timeentries() : HasMany{
        return $this->hasMany(Timeentries::class, 'Client', 'Code');
    }

    public function projects():HasMany{
        return $this->hasMany(Projects::class, 'Client', 'Code');
    }
}

app\Models\Projects

class Projects extends Model
{
    public function clients(): BelongsTo
    {
        return $this->belongsTo(Clients::class, 'Client', 'Code');
    }

    public function client() : HasOne
    {
        return $this->hasOne(Clients::class, 'Client', 'Code');
    }

    public function timeentries(): HasMany
    {
        return $this->hasMany(Timeentries::class, 'Project', 'Code');
    }

    public function budgets() : HasMany
    {
        return $this->hasMany(Budget::class, 'Project', 'Code');
    }
}

app\Models\Budgets

class Budgets extends Model
{

    public function project() : BelongsTo{
        return $this->belongsTo(Projects::class, 'Project', 'Code');
    }
}

app\Models\Timeentries

class Timeentries extends Model
{
    public function project():BelongsTo{
        return $this->belongsTo(Projects::class, 'Project', 'Code');
    }


    public function client():BelongsTo{
        return $this->belongsTo(Clients::class, 'Client', 'Code');
    }

    public function employee():BelongsTo{
        return $this->belongsTo(Employees::class, 'Employee', 'Code');
    }

    public function getProject() : HasOne{
        return $this->hasOne(Projects::class, 'Code', 'Project');
    }


    public function getEmployee() : HasOne{
        return $this->hasOne(Employees::class, 'Code', 'Employee');
    }
}

This is my query so far:

$clients = Clients::where('Active', 'Y')
            ->with([
                'projects' => [
                    'budgets',
                    'timeentries',
                ],
            ])
            ->whereHas('projects', function(Builder $query){
                $query->where('Active', 'Y');
            })
            ->has('projects.budgets')
            ->orderBy('name', 'asc')
            ->get();

The above will list all projects of a client whether the project has a budget(s) set for it or not. I want only the ones that have a budget set.

I have a hunch I need an additional whereHas that specifies projects.budgets not null or similar but can't quite work that out. Any ideas/suggestions appreciated!

At the moment I'm having to filter within my foreach loops, there has to be a better way.

foreach($clients as $client){
            foreach($client->projects as $project){
                if($project->budgets->count()){

                    $totalBudget = $project->budgets->sum('Cost');
                    $totalBudgetTime = $project->budgets->sum('Time');

                    dump($project->Code.' '.$project->name.' - BudgetValue: '.$totalBudget.' - BudgetTime: '.$totalBudgetTime);
                }
            }
        }