Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hi all, i have a very strange problem.
I'm joining tables with this query:
$studentsCount = DB::table('cities as c')
->select('c.id', 'c.lat', 'c.lng', 'ct.name', 'ct.slug', DB::raw('count(sj.student_id) as student_count'))
->join('city_translations as ct', 'ct.city_id', '=', 'c.id')
->join('companies as comp', 'comp.city_id', '=', 'c.id')
->join('student_jobs as sj', 'sj.company_id', '=', 'comp.id')
->join('students as s', 's.id', '=', 'sj.student_id')
->where('ct.locale', $locale)
->where('s.active', 1)
->groupBy('c.id')
->get();
When I run that query from MySQL workbench it works fine no aggregate error. Also tried to run it from cli on Homestead where MySQL workbench is connecting.
I got the real sql with (without ->get()):
$studentsCount->toSql()
The aggregate error appears when I use that query in laravel:
Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'alumni.ct.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `c`.`id`, `c`.`lat`, `c`.`lng`, `ct`.`name`, `ct`.`slug`, count(sj.student_id) as student_count from `cities` as `c` inner join `city_translations` as `ct` on `ct`.`city_id` = `c`.`id` inner join `companies` as `comp` on `comp`.`city_id` = `c`.`id` inner join `student_jobs` as `sj` on `sj`.`company_id` = `comp`.`id` inner join `students` as `s` on `s`.`id` = `sj`.`student_id` where `ct`.`locale` = sr and `s`.`active` = 1 group by `c`.`id`) in file /home/vagrant/projects/vts-alumni/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 664
Stack trace:
When I add the nonaggregated fields to the query this way:
->groupBy('c.id', 'ct.name', 'ct.slug')
It works.
I don't understand how the error is not appearing from mysql cli and workbench but appearing from laravel. Laravel is connecting to the same mysql server where I run my tests from cli and workbench.
Does your database.php config include a strict option set to true; this adds 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
Solution is to change the connection config:
'mysql' => [
// ...
'strict' => false,
]
Yes but the ONLY_FULL_GROUP_BY is enabled in mysql by default now. Some of the other modes are having an impact on this too?
Thanks
The connection that Laravel is making still has ONLY_FULL_GROUP_BY after setting 'strict' => false?
When I set 'strict' => false works fine I just don't understand what added the additional aggregate exception while ONLY_FULL_GROUP_BY is enabled on my server by default.
Well, it mustn't be enabled by default if your other connections (MySQLWorkbench / CLI) did not encounter the error. You can check by executing SELECT @@sql_mode; query in the different clients.
The background is MySQL5.7 enables strict modes by default - this is a change since MySQL 5.6
I have it enabled from cli too. The version is 5.7.22
Server version: 5.7.22-0ubuntu18.04.1 (Ubuntu)
mysql> SELECT @@sql_mode;
--------------
SELECT @@sql_mode
--------------
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
And when I run this query:
mysql> select `c`.`id`, `c`.`lat`, `c`.`lng`, `ct`.`name`, `ct`.`slug`, count(sj.student_id) as student_count from `cities` as `c` inner join `city_translations` as `ct` on `ct`.`city_id` = `c`.`id` inner join `companies` as `comp` on `comp`.`city_id` = `c`.`id` inner join `student_jobs` as `sj` on `sj`.`company_id` = `comp`.`id` inner join `students` as `s` on `s`.`id` = `sj`.`student_id` where `ct`.`locale` = 'sr' and `s`.`active` = 1 group by `c`.`id`;
--------------
select `c`.`id`, `c`.`lat`, `c`.`lng`, `ct`.`name`, `ct`.`slug`, count(sj.student_id) as student_count from `cities` as `c` inner join `city_translations` as `ct` on `ct`.`city_id` = `c`.`id` inner join `companies` as `comp` on `comp`.`city_id` = `c`.`id` inner join `student_jobs` as `sj` on `sj`.`company_id` = `comp`.`id` inner join `students` as `s` on `s`.`id` = `sj`.`student_id` where `ct`.`locale` = 'sr' and `s`.`active` = 1 group by `c`.`id`
--------------
+----+-------------+---------------+-------------------+-------------------+---------------+
| id | lat | lng | name | slug | student_count |
+----+-------------+---------------+-------------------+-------------------+---------------+
| 1 | 37.40930700 | -116.83705300 | sr-Schummmouth | sr-schummmouth | 2 |
| 3 | -5.65479800 | 141.62207800 | sr-New Jon | sr-new-jon | 1 |
| 5 | 12.83302800 | -138.16928200 | sr-East Bryanaton | sr-east-bryanaton | 1 |
+----+-------------+---------------+-------------------+-------------------+---------------+
3 rows in set (0.00 sec)
It works with only group by c.id
from laravel I must add the other 2 columns to the group by when 'strict'=>true
Please or to participate in this conversation.