Eloquent groupBy does "SQLSTATE [42000]" with a valid SQL query in Laravel 5.3

Question

I have a strange problem with Eloquent which I am trying to do as follows:

$this->node = \DB::table('permission') ->select('permission.id', 'object.name as object_name', 'permission.created_at', 'object.id as object_id') ->join('object', 'object.id', '=', 'permission.object_id') ->join('action', 'action.id', '=', 'permission.action_id') ->where('permission.person_id', $this->person['id']) ->groupBy('permission.object_id') ->orderBy('permission.created_at', 'desc') ->paginate(5); 

Laravel Framework will report an error:

QueryException on line 761 Connection.php: SQLSTATE [42000]: syntax error or access violation: 1055 'permission.id' is not in GROUP BY (SQL: select permission . id , object . name as object_name , permission . created_at , object . id as object_id of permission internal connection of object to object = permission . object_id internal connection of action to action . id = permission . action_id where permission . person_id = 1 group by permission . object_id order permission . created_at restriction descending 5 offset 0)

I added the Eloquent DB :: listen debugging function to the AppServiceProvider:

 use Illuminate\Support\Facades\DB; use Illuminate\Support\ServiceProvider; class AppServiceProvider extends ServiceProvider { /** * Bootstrap any application services. * * @return void */ public function boot() { // DB::listen(function ($query) { echo "<pre>"; print_r($query->sql); echo "</pre>"; // $query->sql // $query->bindings // $query->time }); } ... 

And he prints this SQL query:

 select `permission`.`id`, `object`.`name` as `object_name`, `permission`.`created_at`, `object`.`id` as `object_id` from `permission` inner join `object` on `object`.`id` = `permission`.`object_id` inner join `action` on `action`.`id` = `permission`.`action_id` where `permission`.`person_id` = 1 group by `permission`.`object_id` order by `permission`.`created_at` desc limit 5 offset 0 

What is really in MySQL through PhpMyAdmin , and here is the result for the query: Even So, I tested the command directly in mysql and it works fine, look at the mysql output:

Any idea?

thanks

Answer 1

Faced the same issue with laravel 5.3 They are trying to force a strict write of requests using mysql-5.7

However, for this, disable it only with config/database.php and change the strict flag

 'mysql' => [ . . . 'strict' => false, //'strict' => true, . . ], 

Hope this solves your problem too.

Answer 2

This query is sql standard and is valid only in mysql in certain sql mode settings. See mysql documentation in MySQL GROUP BY Processing :

SQL92 and earlier do not allow queries for which the selected list, the HAVING clause or ORDER BY clause refers to non-aggregated columns that are not named in the GROUP BY clause and are not functional depending on the GROUP BY columns (uniquely determined). For example, this query is illegal in standard SQL92, because the non-aggregated column name in the selection list does not appear in GROUP BY:

SELECT o.custid, c.name, MAX (o.payment) FROM orders AS o, customers AS with WHERE o.custid = c.custid GROUP BY o.custid; For a query to be legal in SQL92, name columns must be omitted from the selected list or name in the GROUP BY clause.

SQL99 and later versions allow such non-aggregates for each optional T301 function if they are functionally dependent on GROUP BY columns: if such a relationship exists between the name and custid, the request is legal. This for example, would be custid the primary key of customers.

You either need to disable the only_full_group_by sql mode (it is also part of sql strict mode), or use the any_value () function in the selection list for non-aggregated fields that are not in the group by section.

 SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;