orWHere clause with eggregate sum function

i want to calcul the sum of spent_hours in the pivot table using sum for one condition it work perectly

            Tables\Columns\TextColumn::make('employees_sum_spent_hours')
                ->label("charge déporte (réelle)")
                ->getStateUsing(function (Task $record): int {
                    $spentHours = $record->employees
                        ->where('pivot.role', "pri_ass")
                        ->sum('pivot.spent_hours');

                    return $spentHours;
                })

but when add (or) condition like this

                      Tables\Columns\TextColumn::make('employees_sum_spent_hours')
                ->label("charge déporte (réelle)")
                ->getStateUsing(function (Task $record): int {
                    $spentHours = $record->employees
                        ->where(function ($query) {
                            $query->where('pivot.role', 'pri_ass')
                                ->orWhere('pivot.role', 'sec_ass');
                        })
                        ->sum('pivot.spent_hours');

                    return $spentHours;
                }),

it returns 0 but mathematically it must at least return the value that it returns earlier when I use one condition

                        ->where('pivot.role', "pri_ass")

there is any help laravel community !!! for me, i think this issue comes from

->sum('pivot.spent_hours')

aggregate function and how it behaves

kevinbui

2 years ago

Best Answer

Level 41

You might already know this.

When we access the employees relationship like a property like this, you got a collection:

$record->employees;

If we want to work with a database query scoped by that $record, we do this:

$record->employees();

With that, I reckon the following might work:

return $record->employees()
    ->where(fn ($query) =>$query->wherePivotIn('role', ['pri_ass', 'sec_ass']))
    ->get()
    ->sum('pivot.spent_hours');

Or you can try this:

return $record->employees
    ->filter(fn ($employee) =>in_array($employee->pivot->role, ['pri_ass', 'sec_ass']))
    ->get()
    ->sum('pivot.spent_hours');

1 like

medcharrafi

2 years ago

Level 48

@kevinbui one thing to notice we don't need ->get()

here

return $record->employees
    ->filter(fn ($employee) =>in_array($employee->pivot->role, ['pri_ass', 'sec_ass']))
    ->get()
    ->sum('pivot.spent_hours'); 

so the right code is 

return 
$record->employees
    ->filter(fn ($employee) =>in_array($employee->pivot->role, ['pri_ass', 'sec_ass']))
    ->sum('pivot.spent_hours');

1 like

medcharrafi

2 years ago

Level 48

@kevinbui thank you the second the sedond apprach it work perfectly

Please or to participate in this conversation.