Eloquent groupBy make "SQLSTATE[42000]";在 Laravel 5.3 中使用有效的 SQL 查询 [英] Eloquent groupBy make "SQLSTATE[42000]" with valid SQL query in Laravel 5.3

问题描述

我在使用 Eloquent 时遇到了一个奇怪的问题，我正在尝试执行以下操作:

$this->node = DB::table('permission')->select('permission.id','object.name 作为 object_name','permission.created_at','object.id 作为 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')->分页(5);

Laravel Framework 报告错误:

<块引用>

Connection.php 第 761 行中的 QueryException:SQLSTATE[42000]:语法错误或访问冲突:1055 'permission.id' 不在 GROUPBY (SQL: select permission.id, object.name as object_name,permission.created_at, object.id as object_id frompermission 内连接 object 在 object.id =permission.object_id 内连接 action on action.id =permission.action_id 其中 permission.person_id = 1 group bypermission.object_id order by permission.created_at desc limit5 偏移 0)

我在 AppServiceProvider 中添加了 Eloquent 调试功能 DB::listen:

使用 IlluminateSupportFacadesDB;使用 IlluminateSupportServiceProvider;类 AppServiceProvider 扩展了 ServiceProvider{/*** 引导任何应用程序服务.** @return 无效*/公共函数引导(){//DB::listen(function ($query) {echo "
";print_r($query->sql);echo "</pre>";//$query->sql//$query->绑定//$query->time});}...
它确实打印了这个 SQL 查询:
选择`permission`.`id`，`object`.`name` 为 `object_name`，`权限`.`created_at`，`object`.`id` 作为 `object_id`来自`许可``object`.`id` = `permission`.`object_id` 上的内部连接 ​​`object``action`.`id` = `permission`.`action_id` 上的内部连接 ​​`action`其中`permission`.`person_id` = 1按`权限`分组.`object_id`按`permission`排序.`created_at` desc限制 5 偏移 0
在 MySQL 到 PhpMyAdmin 中有效，这里是查询的输出:即便如此，我直接在 mysql 命令中测试，它确实工作得很好，看看 mysql 输出:
有什么想法吗?
谢谢
解决方案
在使用 laravel 5.3 时遇到同样的问题他们试图通过 mysql-5.7
 强制执行严格的查询编写
但是要禁用它，只需转到 config/database.php 并更改 strict 标志
'mysql' =>[...'严格' =>错误的，//'严格' =>真的，..],
希望这也能解决您的问题.
PS - 有关严格查询编写的详细信息，请参阅@Shadow 的回答
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 IlluminateSupportFacadesDB;
use IlluminateSupportServiceProvider;

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
解决方案
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

                        
这篇关于Eloquent groupBy make "SQLSTATE[42000]";在 Laravel 5.3 中使用有效的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！