Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

nlavi43's avatar

Group rows by day over multiple days and return latest entry per designated column

Hello,

I have a timesheet application that logs user hours for different jobs on a daily basis. A user can enter hours for a given job any number of times in a day, and the application will only ever display the latest row (referencing 'created_at') for that job. I can return the latest hours record entered per job for a given day no problem:

return DB::whereDate('created_at', '=', date('Y-m-d'))
                ->groupBy('job')
                ->get();

However now I wish to do the same over a semi-monthly pay period, but I can't figure out how to return only the latest hours record per job per day over multiple days. Doing the following:

return DB::whereDay('hours_date', '>=', date('d', strtotime($this->currentPeriodStartDate)))
                    ->whereDay('hours_date', '<=', date('d', strtotime($this->currentPeriodEndDate)))
                    ->groupBy('hours_date')
                    ->get();

returns a collection of all hours entered per day, but I don't know how to then get back just the latest record entered for each job present. What I want is something like this:

"2017-04-16":[
{
"id":1,
"job":"JOB 1",
"hours":"4.0",
"created_at":"2017-04-16 05:55:50",
"updated_at":"2017-04-17 05:55:50",
"hours_date":"2017-04-16"
},
{
"id":3,
"job":"JOB 2",
"hours":"5.0",
"created_at":"2017-04-16 06:05:30",
"updated_at":"2017-04-17 06:05:30",
"hours_date":"2017-04-16"
}
],
"2017-04-17":[
{
"id":11,
"job":"JOB 1",
"hours":"3.0",
"created_at":"2017-04-17 12:39:16",
"updated_at":"2017-04-17 12:39:16",
"hours_date":"2017-04-17"
},
{
"id":12,
"job":"JOB 2",
"hours":"5.0",
"created_at":"2017-04-17 12:39:32",
"updated_at":"2017-04-17 12:39:32",
"hours_date":"2017-04-17"
}
]
}

where as now it is returning multiple JOB 1's and JOB 2's per day.

I appreciate any help on this!

0 likes
1 reply

Please or to participate in this conversation.