Laravel按日期范围统计指定员工ID总工时并在视图展示的实现方法

我有employes和attendances两张数据表，需求是按指定日期范围统计特定员工ID的work_time字段总和，并在视图中展示该结果。我是Laravel新手，希望通过Laravel Eloquent实现此功能，现有模型、控制器及视图代码如下：

现有代码 #

Employe.php #

class Employe extends Model
{
    use HasFactory;

    protected $fillable=['id','employe_code','employe_name','workplace'];

    
    public function attendances(){ 
        return $this->hasMany('App\Models\Attendance');
    }
}

Attendance.php #

class Attendance extends Model
{
    use HasFactory;
    protected $fillable=['id','employe_id','attendence_date','clock_in','clock_out','work_time','attendence_status'];


    public function employe()
    {
        return $this->belongsTo('App\Models\employe', 'employe_id');
    }
}

AttendanceController.php #

public function attendanceReport(){
   $empname = Employe::get();
    return view('pages.Attendance.attendance_report', compact('empname'));
}

public function attendanceSearch(Request $request){
    $empname = Employe::get();
    if($request->employe_id == 0){
        $emp = Attendance::whereBetween('attendence_date', [$request->from, $request->to])->get();       
        return view('pages.Attendance.attendance_report',compact('emp','empname'));
    }
    else{
        $emp = Attendance::whereBetween('attendence_date', [$request->from, $request->to])
        ->where('employe_id',$request->employe_id)->get();
        return view('pages.Attendance.attendance_report',compact('emp','empname'));
    }
}

attendance_report.blade.php #

<form method="post"  action="{{ route('attendance.search') }}" autocomplete="off" id="sh11">
    @csrf
    <div class="row">
        <div class="col-md-3">
            <div class="form-group">
                <select class="custom-select mr-sm-2"name="employe_id">
                    <option value="0">all</option>
                    @foreach($empname as $empname)
                        <option value="{{ $empname->id }}">{{$empname->employe_name }}</option>
                    @endforeach
                </select>
            </div>
        </div>
        <div class="card-body datepicker-form">
            <div class="input-group" data-date-format="yyyy-mm-dd">
                <span class="input-group-addon">Date</span>
                <input type="text"  class="form-control range-from date-picker-default" value={{ date('Y-m-d') }} required name="from">
                <span class="input-group-addon">to date</span>
                <input class="form-control range-to date-picker-default"  value={{ date('Y-m-d') }} type="text" required name="to">
            </div>
        </div>
    </div>
    <button class="btn btn-success btn-sm nextBtn btn-lg pull-right" type="submit">{{trans('Students_trans.submit')}}</button>
</form>

@isset($emp)
<div class="table-responsive">
    <table id="datatable" class="table  table-hover table-sm table-bordered p-0" data-page-length="50"
           style="text-align: center">
        <thead>
        <tr>
            <th class="alert-success">#</th>
            <th class="alert-success">code</th>
            <th class="alert-success">name</th>
            <th class="alert-success">work_time</th>
            <th class="alert-success">attendence_date</th>
            <th class="alert-warning">###</th>
        </tr>
        </thead>
        <tbody>
        @foreach($emp as $EMP)
            <tr>
                <td>{{ $loop->index + 1 }}</td>
                <td>{{ $EMP->employe->employe_code }}</td>
                <td>{{ $EMP->employe->employe_name }}</td>
                <td>Hour  {{ $EMP->work_time }} </td>
                <td>{{ $EMP->attendence_date }}</td>
                <td>
                    @if($EMP->attendence_status == 0)
                        <span class="btn-danger">Presence</span>
                    @else
                        <span class="btn-success">Absence</span>
                    @endif
                </td>
            </tr>
        @endforeach
    </table>
</div>
@endisset

修改实现统计功能 #

1. 修复模型关联问题 #

先修正Attendance.php中的关联模型名（匹配类名首字母大写），避免关联查询失败：

public function employe()
{
    return $this->belongsTo('App\Models\Employe', 'employe_id');
}

2. 调整控制器查询逻辑 #

修改AttendanceController.php的attendanceSearch方法，实现按员工分组统计work_time总和：

public function attendanceSearch(Request $request){
    $empname = Employe::get();
    
    // 可选：添加日期格式验证，增强健壮性
    $request->validate([
        'from' => 'required|date',
        'to' => 'required|date|after_or_equal:from',
    ]);

    if($request->employe_id == 0){
        // 统计所有员工的总工时，按员工ID分组
        $emp = Attendance::whereBetween('attendence_date', [$request->from, $request->to])
            ->select('employe_id', \DB::raw('SUM(work_time) as total_work_time'))
            ->groupBy('employe_id')
            ->with('employe') // 预加载员工信息，避免N+1查询
            ->get();
    } else {
        // 统计单个员工的总工时
        $totalWorkTime = Attendance::whereBetween('attendence_date', [$request->from, $request->to])
            ->where('employe_id', $request->employe_id)
            ->sum('work_time');
        
        // 封装数据结构，和批量统计保持一致，方便视图统一处理
        $emp = collect([
            (object)[
                'employe' => Employe::find($request->employe_id),
                'total_work_time' => $totalWorkTime
            ]
        ]);
    }

    return view('pages.Attendance.attendance_report', compact('emp', 'empname'));
}

3. 更新视图展示统计结果 #

修改attendance_report.blade.php中的表格部分，展示员工总工时：

@isset($emp)
<div class="table-responsive">
    <table id="datatable" class="table  table-hover table-sm table-bordered p-0" data-page-length="50"
           style="text-align: center">
        <thead>
        <tr>
            <th class="alert-success">#</th>
            <th class="alert-success">code</th>
            <th class="alert-success">name</th>
            <th class="alert-success">Total Work Time (Hours)</th>
        </tr>
        </thead>
        <tbody>
        @foreach($emp as $EMP)
            <tr>
                <td>{{ $loop->index + 1 }}</td>
                <td>{{ $EMP->employe->employe_code }}</td>
                <td>{{ $EMP->employe->employe_name }}</td>
                <td>{{ $EMP->total_work_time }}</td>
            </tr>
        @endforeach
    </table>
</div>
@endisset

如果需要同时展示每日考勤明细和总工时，可修改视图为：

@isset($emp)
@if(request()->employe_id != 0)
<!-- 每日考勤明细 -->
<div class="table-responsive mb-3">
    <h5>每日考勤明细</h5>
    <table class="table table-hover table-sm table-bordered p-0" style="text-align: center">
        <thead>
        <tr>
            <th>#</th>
            <th>Date</th>
            <th>Work Time (Hours)</th>
            <th>Status</th>
        </tr>
        </thead>
        <tbody>
        @foreach(Attendance::whereBetween('attendence_date', [request()->from, request()->to])->where('employe_id', request()->employe_id)->get() as $record)
            <tr>
                <td>{{ $loop->index + 1 }}</td>
                <td>{{ $record->attendence_date }}</td>
                <td>{{ $record->work_time }}</td>
                <td>
                    @if($record->attendence_status == 0)
                        <span class="btn-danger">Presence</span>
                    @else
                        <span class="btn-success">Absence</span>
                    @endif
                </td>
            </tr>
        @endforeach
        </tbody>
    </table>
</div>
@endif

<!-- 总工时统计 -->
<div class="table-responsive">
    <h5>总工时统计</h5>
    <table class="table table-hover table-sm table-bordered p-0" style="text-align: center">
        <thead>
        <tr>
            <th>#</th>
            <th>code</th>
            <th>name</th>
            <th>Total Work Time (Hours)</th>
        </tr>
        </thead>
        <tbody>
        @foreach($emp as $EMP)
            <tr>
                <td>{{ $loop->index + 1 }}</td>
                <td>{{ $EMP->employe->employe_code }}</td>
                <td>{{ $EMP->employe->employe_name }}</td>
                <td>{{ $EMP->total_work_time }}</td>
            </tr>
        @endforeach
    </table>
</div>
@endisset

内容的提问来源于stack exchange，提问作者amr_khlousy