我对Eloquent有一个奇怪的问题,我正在尝试执行以下操作:
$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 报告错误:
Connection.php第761行中的QueryException:SQLSTATE [42000]:语法错误或访问冲突:1055'permission.id'不在GROUP BY中(SQL:select permission.id,object.name as object_name,permission.created_at, object.id as object_id from object inner object object on object.id = permission.object_id inner join 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 )
我在AppServiceProvider中添加了一个Eloquent调试函数 DB::listen :
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输出:
任何的想法?
谢谢
2 回答
laravel 5.3面临同样的问题他们正试图强制执行严格的查询写作
mysql-5.7
但是要禁用此功能,只需转到
config/database.php
并更改strict
标志希望这也能解决你的问题 .
此查询针对sql标准,仅在某些sql模式设置下的mysql中有效 . 请参阅MySQL Handling of GROUP BY上的mysql文档:
您需要禁用only_full_group_by sql模式(它也是严格的sql模式的一部分),或者在选择列表中使用any_value()函数用于不在group by子句中的非聚合字段 .