Syntax error or access violation: 1055 'posts.views' isn't in GROUP BY

While working with Laravel database query builder, there may be you need to grouping records with specific column.

For that, you may use the Laravel's groupBy() method to group the record with specific column. But sometimes, even if your query is ok, still you get error in Laravel even if row query works perfect in MySQL command line.

Here is query example.

$posts = DB::table('posts')
    ->groupBy('views')
    ->get();

And the query returns with the bellow error.

After digging dipper about the error, I found the reason. It is due to the MySQL server version 5.7.5+. From this version on the way GROUP BY works is changed since they make it behave in order to be SQL99 compliant, whereas in previous versions it was not.

To overcome this error, either we have to do a full groupBy or change MySQL server configuration. Here is my simple solution.

In the config/database.php file, change the 'strict' => true, to 'strict' => false, in 'connections' => 'mysql' array. This will change MySQL's strict mode to disable.

'connections' => [
    ...
    'mysql' => [
       'strict' => false
    ]
]

I hope this article will help you to understand the error and solving in your work. If you liked it, please do share with your friends.

Author : Harsukh Makwana