雄辩的组别使“SQLSTATE [42000]”在Laravel 5.3中使用有效的SQL查询 [英] Eloquent groupBy make "SQLSTATE[42000]" with valid SQL query in Laravel 5.3

问题描述

  $ this->节点我有一个奇怪的问题， = \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 报告错误：

Connection.php行中的QueryException行761：SQLSTATE [42000]：语法
错误或访问冲突：1055permission.id不在GROUP $ b中$ b BY（SQL：select permission 。 id ， object 。 name as object_name ，
permission created_at ， object 。 id as object_id 从
权限 inner join object on 对象。 id =
权限。 c code> =
权限。 action_id 其中权限。 person_id = 1 group by
权限。 object_id order by permission 。 created_at desc limit
5我已经在AppServiceProvider中添加了一个Eloquent调试功能 DB :: listen ：



$ b ：

 使用Illuminate\Support\Facades\DB; 
使用Illuminate\Support\ServiceProvider; 
 
 class AppServiceProvider扩展ServiceProvider 
 {
 / ** 
 *引导任何应用程序服务。 
 * 
 * @return void 
 * / 
 public function boot（）
 {
 // 
 DB :: listen（function $查询）{
 
 echo< pre>; 
 print_r（$ query-> sql）; 
 echo< / pre>; 
 
 // $ query-> sql 
 // $ query-> bindings 
 // $ query-> time 
}）; 
} 
 ... 
  

它打印此SQL查询： / p>



  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` 
内部连接`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 
  

哪些在中通过 PhpMyAdmin 有效，这里是查询的输出：

即使如此，我直接在 mysql 命令中测试，它的工作正常，看看mysql输出：

任何想法？ / p>

感谢

解决方案

面对同样的问题，laravel 5.3
他们试图执行严格的查询写入与 mysql-5.7

但是要禁用这只是去 config / database.php 并更改 strict 标志

 'mysql'=> [
。 
。 
。 
'strict'=> false，
 //'strict'=>真，
。 
。 
]，
  

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

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

解决方案

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.

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