Does laravel automatically add group by's? Weird behaviour Laravel toSql vs query being run.

Question

I have a very, VERY weird issue. Our team has ignored this for a while because it hasn't broken anything (I know, not a good attitude), but I really want to know what the hell is/could be going on. We are on Laravel 5.2.

We have built very long queries using the laravel eloquent query builder. For some reason, sometimes, when we do a toSql(), we'll get the query we meant to run. When pasting the query, I'll get an error like this one:

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'onion.distance' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Weird part though, changing nothing about it, laravel will run the query just fine! Anyone have ANY idea why laravel would run the query fine, but the toSql out put trigger this error? And how could we avoid it (other than manually adding the Group By's).

Not pasting code unless requested (lots of moving parts to some of the queries).

Take a look at https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql - this is connected to MySql version. — Marcin Nabiałek, Dec 27 '17 at 23:58
Probably strict mode is dasabled in `config/database.php`, but enabled by default on the server. — Paul Spiegel, Dec 28 '17 at 00:06

score 0 · Accepted Answer · answered Jan 10 '18 at 22:23

Thank you to Marcin Nabialek, you were 100% right. Laravel was overriding the default mysql server configuration and setting strict mode to false. For anyone that was confused as I was:

database.php

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', 'localhost'),
    'port' => env('DB_PORT', '3306'),
    'dump_command_timeout' => 60 * 15, // 5 minute timeout
    'unix_socket' => env('UNIX_SOCKET', ''),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'root'),
    'password' => env('DB_PASSWORD', 'mysqltest'),
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
]

All I had to do was set strict to true

'strict' => true

Does laravel automatically add group by's? Weird behaviour Laravel toSql vs query being run.

1 Answers1