Level 10
It's mostly "nested" group by and map using collection to get the result you want.
Not much mistery, you only need to go one step at a time, group by group
$result = ActivityLog::query()
->whereNotNull('start')
->whereNotNull('end')
->get()
->map(function($row) {
$row->diffInseconds = $row->start->diffInseconds($row->end);
return $row;
})
->groupBy(fn ($row) => $row->start->format('Y-m-d'))
->map(function($dates) {
return $dates
->groupBy('user_id')
->map(function($userEntries) {
$total = $userEntries->sum('diffInseconds');
return $userEntries
->map(function($entry) use ($total) {
$entry->total = $total;
return $entry;
})
->groupBy('total');
});
});
You could calculate the difference in seconds with the database using selectRaw().
If you make the calculation with php like here then make sure your activity log eloquent model has $casts of "start" and "end" as datetime, or that first map will blow up
1 like