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)

zahid hasan emon · Accepted Answer · 2020-07-07 07:58:42Z

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 7, 2020 at 7:58

zahid hasan emon

6,2333 gold badges21 silver badges31 bronze badges

answered Mar 21, 2018 at 7:37

Rp9

1,9612 gold badges27 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zahid hasan emon Over a year ago

crap! forgot to run the command. thanks mate. it is working now

Rp9 Over a year ago

@zahidhasanemon if its correct please upvote and accept

Simone Cabrino · Accepted Answer · 2018-03-21 07:49:29Z

4

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

answered Mar 21, 2018 at 7:49

Simone Cabrino

93110 silver badges24 bronze badges

1 Comment

zahid hasan emon Over a year ago

i said i have used these solutions. you are just copying the answers from the other questions. read the question fully first.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related