I wish to achieve such a table:

I currently have this table:

EDIT!!

This is my attendance table in My sql.

This is the attendance table in mysql:

This is the Staff Table in mySql

Dashboard Controller

 public function index()
    {
           $data = [
           'attendances'=>Attendance::query()
               ->where('type','!=','255')
                ->with('staff')->get()
        ];


        return  view('dashboard.attendance_table',$data);


//        $attendances = Attendance::all(); // or whatever Eloquent query you want that returns a collection
//
//        $grouped = $attendances->groupBy('staff_id');
//
//        dd($grouped);
    }

attendance.blade

 <div class="row">
                    <div class="col-lg-12">
                        <div class="card overflow-hidden">
                            <div class="card-body">
                                <div>
                                    <h6 class="main-content-label mb-1">Attendance Table</h6>

                                </div>
                                <div class="table-responsive">
                                    <table id="exportexampl" class="table table-bordered border-t0 key-buttons text-nowrap w-100 display " >
                                        <thead>
                                        <tr>
                                            <th>#</th>
                                            <th>Staff Name</th>
                                            <th>User ID</th>
                                            <th>Time In/ Time Out</th>
                                            <th>Card No.</th>
                                            <th>Date</th>
                                            <th>Time</th>

                                        </tr>
                                        </thead>
                                        <tbody>
                                        @foreach($attendances as $attendance)
                                            <tr>
                                                <td>{{$loop->iteration}}</td>
                                                <td>{{ $attendance->staff->name }}</td>
                                                <td>{{ $attendance->staff->userid }}</td>
                                                {{--                                          <td>{{gettype($attendance->type)}}</td>--}}
                                                <td>{{ ($attendance->type==0) ? 'Time in': 'Time out'}}</td>
                                                <td>{{ $attendance->staff->cardno }}</td>
                                                <td>{{ \Carbon\Carbon::parse($attendance->timestamp)->toDateString() }}</td>
                                                <td>{{ \Carbon\Carbon::parse($attendance->timestamp)->format("H:i:s") }}</td>
                                            </tr>
                                        @endforeach
                                        </tbody>
                                    </table>
                                </div>
                            </div>

How would you group the current attendance table so as to achieve my desired attendance table as shown above. Note: Time in is 'type'=0 time out is type =1

CodePudding user response：

Try this:

$attendances = Staff::leftJoin('attendances', function($join) { 
    $join->on('attendances.staff_id', '=', 'staff.id')
    ->on('attendances.id', '=', DB::raw("(SELECT max(id) from attendances WHERE attendances.staff_id = staff.id)")); 
})
->select(array('staff.*', 'attendances.time_in as time_in', 'attendances.time_out as time_out'))
->get();

or if time_in and time_out is placed in different row, try this:

Controller:

$staffs = Staff::all();

in your view .blade.php (inside $staffs foreach):

@php
    $time_in = $staff->attendances()->where('type', 0)->orderBy('created_at', 'desc')->first() ? $staff->attendances ()->where('type', 0)->orderBy('created_at', 'desc')->first()->timestamp : '-';

    $time_out = $staff->attendances()->where('type', 1)->orderBy('created_at', 'desc')->first() ? $staff->attendances()->where('type', 1)->orderBy('created_at', 'desc')->first()->timestamp : '-';
@endphp

so you can just call $time_in or $time_out in your <td>{{ $time_in }} - {{ $time_out }}</td>