Eloquent groupBy make "SQLSTATE[42000]";在 Laravel

$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);

使用 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 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！