Laravel Eloquent Model Syntax error or access violation on groupBy

Question

MySQL structure

id
key
display_name
value
details
group
created_at
updated_at

Laravel resource index

    public function index()
    {

        $settings = Setting::groupBy('group')->select('id','group as name')->orderBy('id', 'ASC')->get();
        
        return view('settings.general'), compact('settings'));
    }

When I run the code its how error :

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'demodb.settings.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select id, group as name from settings group by group order by id asc)

also enable strict=>false in database.php mysql connection – John Lobo Jun 14 '21 at 03:59 — John Lobo, Jun 14 '21 at 03:59
that is a very bad idea as it allows bad queries to run – nbk Jun 14 '21 at 15:59 — nbk, Jun 14 '21 at 15:59
i don't want to enable strict mode – Joynal Abedin Jun 16 '21 at 07:08 — Joynal Abedin, Jun 16 '21 at 07:08

score 1 · Answer 1 · answered Jun 13 '21 at 19:35

Every column has to be in the GROUP By or have a aggregation function like in the example MIN(id)

public function index()
{

    $settings = Setting::groupBy('group')->select('MIN(d) AS id,group as name')->orderBy('id', 'ASC')->get();
    
    return view('settings.general'), compact('settings'));
}

score 0 · Answer 2 · answered Jun 13 '21 at 19:27

When your group by 'group' you can have more than one id per group so it's a pb of logic, you cannot* have id in the select list.

*since your query cannot guess

2 solutions depending of what you want to do :

retrieve id from the select
add id to the group by

Laravel Eloquent Model Syntax error or access violation on groupBy

2 Answers2