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!