groupBy in Controller Index function

Question

I have data that I'm trying to groupBy a field on index function

public function index()
    {
        abort_if(Gate::denies('workstep_access'), Response::HTTP_FORBIDDEN, '403 Forbidden');
        $thisUser = Auth::user();
        $worksteps = Workstep::with(['site_id'])->where('site_id', $thisUser->site_id)->get();
        return view('admin.worksteps.process', compact('worksteps'));
    }

Adding ->groupBy('subprocess_id'); before->get(); As below

$worksteps = Workstep::with(['site_id'])->where('site_id', $thisUser->site_id)->groupBy('subprocess_id')->get();

produces error

Syntax error or access violation: 1055 'tool_db.worksteps.id' isn't in GROUP BY

Also tried

$grouped = $worksteps->groupBy('subprocess_id');
return view('admin.worksteps.process', compact('worksteps', 'grouped'));

Didn't work! Can anyone suggest a solution but without a DB query if possible?

Add a ->select('id', 'column1',...) with column names, which you want to get and you want do the sorting. — Sabin Chacko, Jun 23 '23 at 13:26
I just need to groupBy one column, trying to achieve it without db queries — Jessica Kimble, Jun 23 '23 at 13:35
Does this answer your question? [Laravel : Syntax error or access violation: 1055 Error](https://stackoverflow.com/questions/40917189/laravel-syntax-error-or-access-violation-1055-error) — linktoahref, Jun 23 '23 at 13:57

score 0 · Answer 1 · answered Jun 23 '23 at 13:41

0

without a DB query chain ->groupBy('subprocess_id') after ->get()

$worksteps = Workstep::with(['site_id'])->where('site_id', $thisUser->site_id)->get()->groupBy('subprocess_id');

answered Jun 23 '23 at 13:41

kris gjika

531
2
8

Tried that before it gives error: Property [id] does not exist on this collection instance. – Jessica Kimble Jun 23 '23 at 13:43
@JessicaKimble can you show: `Workstep::with(['site_id'])->where('site_id', $thisUser->site_id)->get()->dd();`? – kris gjika Jun 23 '23 at 13:51

score 0 · Answer 2 · answered Jun 23 '23 at 13:56

The solution was in database config file config/database.php

In mysql array, set strict => false to disable MySQL's strict mode only then

$worksteps = Workstep::with(['site_id'])->where('site_id', $thisUser->site_id)->groupBy('subprocess_id')->get();

Worked.

groupBy in Controller Index function

2 Answers2