能言善辩GROUPBY使“SQLSTATE [42000]”在Laravel有效的SQL查询5.3

  • Z时代
  • 2024-01-10
  • 分类：问答

我有洋洋洒洒一个奇怪的问题，我正在努力做到以下几点：能言善辩GROUPBY使“SQLSTATE [42000]”在Laravel有效的SQL查询5.3

$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框架报告错误：

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)

我已经添加了雄辩的调试功能DB ::在听 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 
     }); 
    } 
    ...

而且它打印此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

其中通过在有效的MySQL phpMyAdmin的这里是查询输出： 即使是这样，我测试中mysql命令直接和它工作得很好，看看mysql输出：

任何想法？

感谢

回答：

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

不过来禁用此只是去config/database.php和改变strict标志

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

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

回答：

该查询是对SQL标准，只有在某些特定的SQL模式设置MySQL的有效。见MySQL文档MySQL Handling of GROUP BY：

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:

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.

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.

要么你需要禁用only_full_group_by SQL模式（它是经过严格的SQL模式部分为好），或在选择列表中的非聚集的领域是不能使用any_value()功能在group by子句中。

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

以上是 能言善辩GROUPBY使“SQLSTATE [42000]”在Laravel有效的SQL查询5.3 的全部内容， 来源链接： utcz.com/qa/265522.html

回到顶部