Example 2. Generating a Monthly Report for Employee Work Hours

One of the most common things you'll do with groupBy is to generate reports. For example, you might want to generate a monthly report for employee work hours (timesheet table):

Project Setup

Let's quickly go through the project setup to see what we have:

Migrations

Schema::create('employees', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});
 
Schema::create('employee_timesheets', function (Blueprint $table) {
    $table->id();
    $table->foreignIdFor(Employee::class)->constrained();
    $table->dateTime('start');
    $table->dateTime('end')->nullable();
    $table->timestamps();
});

app/Models/Employee.php

class Employee extends Model
{
    use HasFactory;
 
    protected $fillable = [
        'name'
    ];
 
    public function employeeTimesheets(): HasMany
    {
        return $this->hasMany(EmployeeTimesheet::class);
    }
}

app/Models/EmployeeTimesheet.php

class EmployeeTimesheet extends Model
{
    use HasFactory;
 
    protected $fillable = [
        'employee_id',
        'start',
        'end',
    ];
 
    public function employee(): BelongsTo
    {
        return $this->belongsTo(Employee::class);
    }
}

database/factories/EmployeeFactory.php

class EmployeeFactory extends Factory
{
    protected $model = Employee::class;
 
    public function definition(): array
    {
        return [
            'name' => $this->faker->name(),
        ];
    }
}

database/factories/EmployeeTimesheetFactory.php

class EmployeeTimesheetFactory extends Factory
{
    protected $model = EmployeeTimesheet::class;
 
    public function definition(): array
    {
        $startTime = fake()->dateTimeBetween('-2 weeks');
        return [
            'employee_id' => Employee::factory(),
            'start' => $startTime,
            // Note that here, we take our start time and add 8 hours to it to keep our maximum time per entry to 8 hours
            'end' => fake()->dateTimeBetween($startTime, $startTime->format('Y-m-d H:i:s').' + 8 hours'),
        ];
    }
}

database/seeders/DatabaseSeeder.php

// ...
Employee::factory()
  ->count(10)
  ->has(EmployeeTimesheet::factory()->count(14))
  ->create();

That's it. Now we have ten employees with 14 timesheets each:

Generating the Report

To generate our report, we will use the following code:

app/Http/Controllers/EmployeeTimesheetController.php

use App\Models\EmployeeTimesheet;
use DB;
 
// ...
 
public function __invoke()
{
    $timesheet = EmployeeTimesheet::query()
        ->select('employee_id')
        // We will use this to calculate the total hours
        // TIMEDIFF will give us the difference between two times - end and start
        // TIME_TO_SEC will convert the time to seconds
        // SUM will sum all the seconds
        // SEC_TO_TIME will convert the seconds to time (00:00:00 format)
        ->addSelect(
            DB::raw('SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(end, start)))) as `total_hours`'),
        )
        // We will use this to get the earliest start time
        ->addSelect(
            DB::raw('min(start) as `min_start`'),
        )
        // We will use this to get the latest end time
        ->addSelect(
            DB::raw('max(end) as `max_end`'),
        )
        // We will use this to get the total days
        ->addSelect(
            DB::raw('count(distinct(start)) as `total_days`')
        )
        // Here, we apply the filters only to take the current month
        ->where('start', '>=', now()->startOfMonth())
        ->where('end', '<=', now()->endOfMonth())
        // Preloading employee details in a relationship
        ->with(['employee'])
        // Grouping by employee id
        ->groupBy('employee_id')
        // Ordering by total hours
        ->orderBy('total_hours', 'desc')
        ->get();
 
    return view('employee.timesheet', ['timesheet' => $timesheet]);
}

resources/views/employee/timesheet.blade.php

<table class="table-auto w-full mt-4">
    <thead>
    <tr>
        <th class="px-4 py-2">Employee</th>
        <th class="px-4 py-2">Entries</th>
        <th class="px-4 py-2">Total Time</th>
        <th class="px-4 py-2">Earliest Date</th>
        <th class="px-4 py-2">Latest Date</th>
    </tr>
    </thead>
    <tbody>
    @foreach($timesheet as $entry)
        <tr>
            <td class="border px-4 py-2">{{ $entry->employee->name }}</td>
            <td class="border px-4 py-2">{{ $entry->total_days }}</td>
            <td class="border px-4 py-2">{{ $entry->total_hours }}</td>
            <td class="border px-4 py-2">{{ $entry->min_start }}</td>
            <td class="border px-4 py-2">{{ $entry->max_end }}</td>
        </tr>
    @endforeach
    </tbody>
</table>

This gives us two queries that are executed:

select `employee_id`,
       SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(end, start)))) as `total_hours`,
       min(start)                                          as `min_start`,
       max(end)                                            as `max_end`,
       count(distinct (start))                             as `total_days`
from `employee_timesheets`
where `start` >= '2023-08-01 00:00:00'
  and `end` <= '2023-08-31 23:59:59'
group by `employee_id`
order by `total_hours` desc
 
select *
from `employees`
where `employees`.`id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

And the result is: