RafaelMunoznl
1 month ago

GroupBy after or before GET()

Posted 1 month ago by RafaelMunoznl

I want to group the results of a query per employee:

This work (groupBy after get() ):

$newDay = Appointment::when($employeeId, function ($query, $employeeId) {
                $query->where('employee_id', $employeeId);
            })
                ->where('company_id', $companyId)
                ->whereDate('startDateTime', 'like', '%' . $datum . '%')
                ->orderBy('employee_id')
                ->orderBy('startDateTime')
                ->select('company_id', 'employee_id', 'client_id', 'startDateTime', 'endDateTime')
                ->selectRaw('appointments.*, TIMESTAMPDIFF(minute, startDateTime, endDateTime) as duration, TIMESTAMPDIFF(minute, startDateTime, endDateTime) / 15 as rows')
                ->get()
                ->groupBy('employee_id');

This does not work (groupBy before get() ):

$newDay = Appointment::when($employeeId, function ($query, $employeeId) {
                $query->where('employee_id', $employeeId);
            })
                ->where('company_id', $companyId)
                ->whereDate('startDateTime', 'like', '%' . $datum . '%')
                ->orderBy('employee_id')
                ->orderBy('startDateTime')
                ->select('company_id', 'employee_id', 'client_id', 'startDateTime', 'endDateTime')
                ->selectRaw('appointments.*, TIMESTAMPDIFF(minute, startDateTime, endDateTime) as duration, TIMESTAMPDIFF(minute, startDateTime, endDateTime) / 15 as rows')
                ->groupBy('employee_id')
                ->get();

I get following exception:

Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'friseur-oktober.appointments.company_id' which is not functionally dependent on columns in GROUP BY clause;

I would go with the second approach, but, as I understand this grouping is made AFTER fetching data from the database, not done by the database server. Which could lead to performance issues.

However, "the right way" does not work. The question is why? Am I doing something wrong?

Please sign in or create an account to participate in this conversation.