沃梦达 / 编程问答 / php问题 / 正文

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

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

本文介绍了Eloquent groupBy make "SQLSTATE[42000]";在 Laravel 5.3 中使用有效的 SQL 查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在使用 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 内连接 objectobject.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

MySQLPhpMyAdmin 中有效,这里是查询的输出:即便如此,我直接在 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 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本文标题为:Eloquent groupBy make "SQLSTATE[42000]";在 Laravel

基础教程推荐