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

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)

score 18 · Accepted Answer · edited Jul 07 '20 at 07:58

18

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',''));

edited Jul 07 '20 at 07:58

zahid hasan emon

6,023
3
16
28

answered Mar 21 '18 at 07:37

Rp9

1,955
2
23
31

crap! forgot to run the command. thanks mate. it is working now – zahid hasan emon Mar 21 '18 at 07:55
@zahidhasanemon if its correct please upvote and accept – Rp9 Mar 21 '18 at 07:55

score 4 · Answer 2 · answered Mar 21 '18 at 07:49

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',
        ],
    ]
]

i said i have used these solutions. you are just copying the answers from the other questions. read the question fully first. — zahid hasan emon, Mar 21 '18 at 08:22

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

2 Answers2