Get all rows with sum if not found set null – Laravel Eloquent

  eloquent, laravel, php

I have two models: Employee and WorkingDay.

//Employee Model

public function workingDays()
    {
        return $this->hasMany(WorkingDay::class, 'employee_id','id');
    }

public function scopeTotalHours($query, $from, $to)
    {
        $query->whereHas('workingDays', function ($query) use ($from, $to) {
            $query->whereBetween('work_date', [$from, $to]);
        })->withSum('workingDays', 'hours_work');
    }

I want to receive sum of hours for each employee between specified date. But if employee doesnt exist in working_days table, set value to 0 or null

//Query

Employee::totalHours($week->start->toDateTimeString(), $week->end->toDateTimeString())->get()

Finally I want something like this:

Between: 2020-10-31 and 2020-11-05

'Joan Hose', 25
'Martin Walker', 0 or null

Thanks

Source: Laravel

Leave a Reply