Eloquent groupBy make “SQLSTATE[42000]” with valid

问题:

I have a strange problem with Eloquent which I'm trying to do the following:

$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 report an Error:

QueryException in Connection.php line 761: SQLSTATE[42000]: Syntax error or access violation: 1055 'permission.id' isn't in GROUP BY (SQL: 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)

I've added an Eloquent debugging function DB::listen in 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 it does print 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

Which is valid in MySQL through PhpMyAdmin and here is the output for the query: Even So, I tested in mysql command directly and it does work just fine, look at mysql output:

Any idea?

Thanks

回答1:

Faced same problem with laravel 5.3 They are trying to enforce strict query writing came with mysql-5.7

However to disabled this just go to config/database.php and change strict flag

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

Hope this will solve your problem too.

PS - For details on strict query writing refer to @Shadow's answer

回答2:

This query is against the sql standard and is only valid in mysql under certain sql mode settings. See mysql documentation on MySQL Handling of GROUP BY:

SQL92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns. For example, this query is illegal in standard SQL92 because the nonaggregated name column in the select list does not appear in the GROUP BY:

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

SQL99 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns: If such a relationship exists between name and custid, the query is legal. This would be the case, for example, were custid a primary key of customers.

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

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