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

ajsmith_codes's avatar

Possible to do multiple groupBy with resource collection?

I have a database of employee time clock punches. I am currently grouping by the employee. When an employee has two entries for one day, I would like to group those together.

Each employee has multiple punch entries. Example of current table structure:

Employee ID			Date				In						Out					Total
16					1-27-22			07:00:00					12:00:00			5
16					1-27-22			12:30:00					04:30:00		4

Is it better to add another table with dates, or keep the table structure as-is?

If it's better to keep it as-is, how do I group by date? Here is my code for getting the info:

        $data = $this->employee->with('punches', 'user')
            ->where('supervisor_id', '=', 7)
            ->has('punches')
            ->withSum('punches as total', 'total_time_converted')
            ->orderBy('employee_num', $request->order)
            ->get();

        return EmployeeResource::collection($data);
0 likes
13 replies
Sinnbeck's avatar

You can perhaps do group by on the collection

$data = $this->employee->with('punches', 'user')
            ->where('supervisor_id', '=', 7)
            ->has('punches')
            ->withSum('punches as total', 'total_time_converted')
            ->orderBy('employee_num', $request->order)
            ->get();

 $data->transform(function($employee) {
    $punches = $employee->punches->groupBy('date');
    $employee->setRelation('punches', $punches):
    return $employee;
}) ;

        return EmployeeResource::collection($data);
ajsmith_codes's avatar

@Sinnbeck When I do that, my vue table shows a column of the same date (today's date) and nothing else.

I am thinking it has to do with how I set this up:

<tbody
                            v-for="employee in pageOfItems"
                            :key="employee.id"
                            class="special">

                        <tr>
                            <td>

                                {{
                                    employee.name
                                }}

                            </td>
                        </tr>

                        <tr
                            v-for="punch in employee.punches"
                            class="text-center">

                            <td>
                                {{
                                    punch.date
                                }}
                            </td>

                            <td>
                                {{
                                    punch.in
                                }}
                            </td>
                            <td>
                                {{
                                    punch.out
                                }}
                            </td>
                        </tr>
                        </tbody>
Sinnbeck's avatar

@ajsmith_codes yeah you can no longer iterate over them on the same way as you are now getting an object of date keys with punches under

Try using console.log in vue to see the structure

ajsmith_codes's avatar

@Sinnbeck

with this:

        <tr
                            v-for="punch in employee.punches"
                            class="text-center">

                            <td>

                                {{
                                    punch
                                }}

                            </td>

</tr>

this is what I get:

[ { "id": 68, "last_name": "Gomez", "first_name": "Jaime", "employee_num": "18", "date": "2022-01-10", "in": "5:42 AM", "out": "4:30 PM", "total_time": "10:48", "dept_code": "4", "in_adjusted": "05:45", "out_adjusted": "04:30", "total_time_adjusted": "10:45", "total_time_converted": "10.75", "account": "Jaime Gomez", "created_at": "2022-01-26T20:13:28.000000Z", "updated_at": "2022-01-26T20:13:28.000000Z" } ]

EDIT: and this is when two share the date:

[ { "id": 77, "last_name": "Gomez", "first_name": "Jaime", "employee_num": "18", "date": "2022-01-21", "in": "3:45 AM", "out": "2:30 PM", "total_time": "10:45", "dept_code": "18", "in_adjusted": "03:45", "out_adjusted": "02:30", "total_time_adjusted": "10:45", "total_time_converted": "10.75", "account": "Jaime Gomez", "created_at": "2022-01-26T20:13:28.000000Z", "updated_at": "2022-01-26T20:13:28.000000Z" }, { "id": 78, "last_name": "Gomez", "first_name": "Jaime", "employee_num": "18", "date": "2022-01-21", "in": "3:00 AM", "out": "4:30 PM", "total_time": "1:30", "dept_code": "18", "in_adjusted": "03:00", "out_adjusted": "04:30", "total_time_adjusted": "01:30", "total_time_converted": "1.50", "account": "Jaime Gomez", "created_at": "2022-01-26T20:13:28.000000Z", "updated_at": "2022-01-26T20:13:28.000000Z" } ]
Sinnbeck's avatar

@ajsmith_codes do you do something to turn it back to an array? Try this in laravel to check the structure

dd($data->first()->toArray());
ajsmith_codes's avatar

@Sinnbeck That breaks it down nicely. Where do I place that code? I can't put it here:

   return EmployeeResource::collection($data->first()->toArray());

EDIT: Nevermind, figured that part out. Now trying to display it.

EDIT: Still getting the same results. Sorry, I posted so quickly. I'll slow down.

Sinnbeck's avatar

@ajsmith_codes no worries. Sadly I don't use vue so I don't know that much about it.

Maybe your resource collection changes it to an array instead of an object. Do you get the data using ajax? If so, try to console.log the response directly to inspect it

Sinnbeck's avatar

Wait. I think I made a mistake. Changed map to transform in the first answer. Sorry

ajsmith_codes's avatar

@Sinnbeck Yes, the resource collection does change it to an array, which is why I didn't see a difference.

How would you do this in PHP? If you have two like the last one, how would you do a loop to show the results?

punches[0]? Something similar? I can't remember.

ajsmith_codes's avatar

@Sinnbeck Results with transform:

[ { "id": 77, "last_name": "Gomez", "first_name": "Jaime", "employee_num": "18", "date": "2022-01-21", "in": "3:45 AM", "out": "2:30 PM", "total_time": "10:45", "dept_code": "18", "in_adjusted": "03:45", "out_adjusted": "02:30", "total_time_adjusted": "10:45", "total_time_converted": "10.75", "account": "Jaime Gomez", "created_at": "2022-01-26T20:13:28.000000Z", "updated_at": "2022-01-26T20:13:28.000000Z" }, { "id": 78, "last_name": "Gomez", "first_name": "Jaime", "employee_num": "18", "date": "2022-01-21", "in": "3:00 AM", "out": "4:30 PM", "total_time": "1:30", "dept_code": "18", "in_adjusted": "03:00", "out_adjusted": "04:30", "total_time_adjusted": "01:30", "total_time_converted": "1.50", "account": "Jaime Gomez", "created_at": "2022-01-26T20:13:28.000000Z", "updated_at": "2022-01-26T20:13:28.000000Z" } ]
ajsmith_codes's avatar

@Sinnbeck Found something interesting with the results, which may be why I'm having issues rendering it:

Punches (object)
2022-01-10: array[1]
2022-01-11: object
2022-01-12: object
etc.

Inside the first date, which is an array, there is an object.

ajsmith_codes's avatar

Maybe this will help. When I get the results from axios, I have to do this to bind it to a variable :

this.tableData = data.data;

That gives the results I showed you.

ajsmith_codes's avatar

Finally got this to work, but it's a bit messy. If anyone else has a way to make this code cleaner, please let me know.

<tr
                            v-for="punch in employee.punches"
                            :key="punch.id"
                            class="text-center">

                            <td>

                                {{
                                    punch[0].date
                                }}

                            </td>

                            <td>

                                <ul class="list-unstyled">

                                    <li v-for="testData in punch">

                                        {{
                                            testData.in
                                        }}

                                    </li>

                                </ul>

                            </td>

     <td>

                                <ul class="list-unstyled">

                                    <li v-for="testData in punch">

                                        {{
                                            testData.out
                                        }}

                                    </li>

                                </ul>

                            </td>

                        </tr>

Please or to participate in this conversation.