Laravel groupBy is not working throwing error Syntax error or access violation: 1055 [duplicate]

Question

I have tried solutions in other questions but that is not solving my issue. I have made strict from true to false. Used modes

'modes' => [           
    'STRICT_TRANS_TABLES',
    'NO_ZERO_IN_DATE',
    'NO_ZERO_DATE',
    'ERROR_FOR_DIVISION_BY_ZERO',
    'NO_AUTO_CREATE_USER',
    'NO_ENGINE_SUBSTITUTION'
] 

as well. But I am getting the same error.

$orders = Order::orderBy('id', 'desc')->groupBy('order_id')->get();
dd($orders);

This is throwing the error

Syntax error or access violation: 1055 'my_db.orders.id' isn't in GROUP BY (SQL: select * from orders group by order_id order by id desc)

(I am using Laravel 5.6)

Answer 1

In config/database.php

Change 'strict' => true To 'strict' => false and clear the cache

php artisan config:cache

OR In MySql settings change

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Answer 2

Short answer

This is a MySQL issue that can be resolved changing Laravel config as @Rp9 wrote, modifying strict => true to strict => false in config/database.php.

Long Answer

MySQL 5.7 introduced something we are calling "strict mode," which is a combination of new modes that, in sum, make MySQL process your queries a little more strictly than before.

"Strict mode" is a list of modes MySQL 5.7 enables by default, comprised of the following modes:

ONLY_FULL_GROUP_BY
STRICT_TRANS_TABLES
NO_ZERO_IN_DATE
NO_ZERO_DATE
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION

So, what if you want to disable / enable just few of this stricts mode? Laravel help us.

'connections' => [
    'mysql' => [
        // Ignore this key and rely on the strict key
        'modes' => null,

        // Explicitly disable all modes, overriding strict setting
        'modes' => [],

        // Explicitly enable specific modes, overriding strict setting
        'modes' => [
            'STRICT_TRANS_TABLES',
            'ONLY_FULL_GROUP_BY',
        ],
    ]
]