Eloquent group在Laravel 5.3中使用有效的SQL查询生成“SQLSTATE [42000]”

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

我和Eloquent有一个奇怪的问题,我正在努力做到以下几点:

$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:

Laravel Framework报告错误:

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)

Connection.php第761行中的QueryException:SQLSTATE [42000]:语法错误或访问冲突:1055'permission.id'不在GROUP BY中(SQL:select permission.id,object.name as object_name,permission.created_at,object .id as object_id from object inner object object on object.id = permission.object_id inner join action on action.id = permission.action_id其中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:

我在AppServiceProvider中添加了一个Eloquent调试函数DB :: listen:

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:

它确实打印了这个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

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:

这在MySQL中通过PhpMyAdmin有效,这里是查询的输出:即便如此,我直接在mysql命令中测试它确实工作得很好,看看mysql输出:

Any idea?

Thanks

2 个解决方案

#1

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

与laravel 5.3面临同样的问题他们正在尝试使用mysql-5.7强制执行严格的查询编写

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

但是要禁用它,只需转到config / database.php并更改严格标志

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

Hope this will solve your problem too.

希望这也能解决你的问题。

#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:

此查询是针对sql标准的,并且仅在某些sql模式设置下的mysql中有效。请参阅有关GROUP BY的MySQL处理的mysql文档:

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:

SQL92和更早版本不允许选择列表,HAVING条件或ORDER BY列表引用非聚合列的查询,这些列既不在GROUP BY子句中命名,也不在功能上依赖于(由GROUP BY列唯一确定)。例如,此查询在标准SQL92中是非法的,因为选择列表中的非聚合名称列不会出现在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.

SELECT o.custid,c.name,MAX(o.payment)FROM orders AS o,customers AS c WHERE o.custid = c.custid GROUP BY o.custid;要使查询在SQL92中合法,必须从选择列表中省略name列,或在GROUP BY子句中命名。

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.

SQL99及更高版本允许每个可选功能T301使用非聚合,如果它们在功能上依赖于GROUP BY列:如果name和custid之间存在这种关系,则查询是合法的。例如,这就是客户的主要关键。

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.

您需要禁用only_full_group_by sql模式(它也是严格的sql模式的一部分),或者在选择列表中使用不在group by子句中的非聚合字段的any_value()函数。

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