How do i calculate and view total work_time for specific id in laravel in one row by date from to?

Question

I have two tables employes and attendances . What I want is to sum work_time for each employe id and show in my view.

employes Table id attendances Table id employe_id work_time

I want to sum work_time column for specific employe id by date from to?. I am new to laravel so how do I do that with 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

Answer 1

you can try to add selectRaw to your releation

 public function attendances(){ 
        return $this->hasMany('App\Models\Attendance')->selectRaw('SUM(work_time) as total_work_time');
    }

check the decumentation