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

ajsmith_codes's avatar

Need help with totaling column in eloquent query.

I've successfully imported all time clock punches of our employees. I'm trying to get a list of the punches grouped under the employee. I want to total the column 'total_time_converted' before sending it to the view.

I tried these two queries and had no luck.

Error for this one: "call to undefined method Illuminate\Database\Eloquent\Builder::mapInto()"

        $query = $this->employee->with('punches', 'user')
            ->where('supervisor_id', '=', 7)
            ->whereHas('punches', function (\Illuminate\Database\Eloquent\Builder $query) {
                return $query->select('employee_punches.*', DB::raw('SUM(employee_punches.total_time_converted) as total'));
            })
            ->orderBy('employee_num', $request->order);

This one gives me the error "Syntax error or violation: 1055 'laravel.employees.id' isn't in GROUP BY

        $query = $this->employee->with('punches', 'user')
            ->where('supervisor_id', '=', 7)
            ->whereHas('punches')
            ->orderBy('employee_num', $request->order)->groupBy('employee_num');

        return PunchResource::collection($query);
0 likes
20 replies
Sinnbeck's avatar

So use a withSum

$query = $this->employee->with('punches', 'user')
            ->where('supervisor_id', '=', 7)
            ->withSum('punches', 'total_time_converted as total'))
            ->orderBy('employee_num', $request->order);
ajsmith_codes's avatar

@Sinnbeck I get this message:

Syntax error or access violation: 1064 You have an error in your SQL syntax;

EDIT: I had to remove the ; from the withSum line and I think that is causing the error.

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

        $data = $query->get();
Sinnbeck's avatar

@ajsmith_codes Ah sorry. I fixed that.

Still getting the error? What is the full error? Can you show the 'punches()' relationship?

You can also just try this

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

and then use it like

@foreach ($data as $item)
    {{$item->punches_sum_total_time_converted}}
@endforeach
ajsmith_codes's avatar

@Sinnbeck Full error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as `total`) from `employee_punches` where `employees`.`employee_num` = `emplo...' at line 1 (SQL: select `employees`.*, (select sum(`employee_punches`.`total_time_converted` as `total`) from `employee_punches` where `employees`.`employee_num` = `employee_punches`.`employee_num`) as `punches_sum_total_time_converted_as_total` from `employees` where `supervisor_id` = 7 and `employees`.`deleted_at` is null order by `employee_num` asc)"

    public function punches(){

        return $this->hasMany('App\Models\Employee\Timecard\Punches', 'employee_num', 'employee_num');

    }
Sinnbeck's avatar

Just dug into the source code. Seems it needs to be aliased like this

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

@Sinnbeck I can now get some data, but not all. It's missing the employee information. I'm sending the results to a Vue instance, just FYI.

There should be 9 punches under the user below, but the array is empty. Also, there are a total of 203 entries for 23 employees and only 3 objects are populated. I hope that makes sense.

Object:
account: null
date: null
employee: null
id: 10
in: null
out: null
punches -> array is empty
user - > object (this is showing the correct user info)
Sinnbeck's avatar
Sinnbeck
4 years ago
Best Answer
Level 102

@ajsmith_codes Try and limit to only have employees withn punches

$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();
ajsmith_codes's avatar

@Sinnbeck I have a nested v-for where the first row of the table shows the employee's name, then the following rows show all the punch entries.

Employees with punches should be the main object, with punches nested under each employee.

Here is a simplified example from Vue:

					<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 can you show your controller code? It sounds like you are removing it somehow. An employee resource collection perhaps?

In your original example it seems that you are using the wrong resource

return PunchResource::collection($query);
//should probably be 
return EmployeeResource::collection($query);
ajsmith_codes's avatar

@Sinnbeck I do use a resource collection.:

       return [
            'employee' => $this->employee,
            'date' => $this->date,
            'user' => $this->user,
            'name' => $this->name,
            'id' => $this->id,
            'user_id' => $this->user_id,
            'last_name' => $this->last_name,
            'employee_num' => $this->employee_number,
            'in' => $this->in,
            'out' => $this->out,
            'total_time' => $this->total_time,
            'in_adjusted' => $this->in_adjusted,
            'out_adjusted' => $this->out_adjusted,
            'total_time_adjusted' => $this->total_time_adjusted,
            'total_time_converted' => $this->total_time_converted,
            'supervisor_id' => $this->supervisor_id,
            'punches' => $this->punches,
            'account' => $this->account,
            'total' => $this->total

        ];
Sinnbeck's avatar

@ajsmith_codes that looks like the wrong one. Should be an employee resource if that is the uppermost collection

Please or to participate in this conversation.