dtcu5885 2016-11-14 01:18
浏览 99
已采纳

对于laravel中的eloquent和query构建器,groupBy无法正常工作

I have a simple Education model contains these fields:

e_id // Primary key
user_id //foreign key to link to User model
field
grade
university
country_education
city_education
date_of_graduation

And this is my Model structure:

class Education extends Model
    {
        public    $primaryKey = 'e_id';
        public    $timestamps = false;
        protected $table      = 'educations';

        protected $fillable = ['user_id', 'field', 'grade', 'university', 'country_education', 'city_education', 'date_of_graduation'];

        public function user ()
        {
            return $this->belongsTo('App\User', 'user_id', 'user_id');
        }

    }

Now I want to select distinct rows based on user_id field. to that I wrote this:

$educations = Education::select(['e_id', 'user_id', 'field', 'grade'])->groupBy(['user_id']);

But below error is occured :

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lms_forms.educations.e_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `e_id`, `user_id`, `field`, `grade` from `educations` group by `user_id` limit 10 offset 0)

then I added e_id primary key to groupBy(['user_id']) :

$educations = Education::select(['e_id', 'user_id', 'field', 'grade'])->groupBy(['user_id','e_id']);

Query runs but return all records regardless of user_id distinction.

What is Problem and What can I do ?

展开全部

  • 写回答

2条回答 默认 最新

  • doumi9618 2016-11-14 01:25
    关注

    You have ONLY_FULL_GROUP_BY mode switched on (it is by default set to ON on MySql 5.7+).

    This is an improvement done on latest mysql version to avoid removal of rows from the query result. Instead of grouping it with PK, try grouping it with other columns to return proper results.

    Or else Disable this mode if you just want to proceed with the results

    See - Disable ONLY_FULL_GROUP_BY

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 单片机 TC277 PWM
  • ¥15 在更新角色衣服索引后,Sprite 并未正确显示更新的效果该如何去解决orz(标签-c#)
  • ¥15 VAE代码如何画混淆矩阵
  • ¥15 求遗传算法GAMS代码
  • ¥15 雄安新区高光谱数据集的下载网址打不开
  • ¥66 android运行时native和graphics内存详细信息获取
  • ¥15 rk3566 Android11 USB摄像头 微信
  • ¥15 torch框架下的强化学习DQN训练奖励值浮动过低,希望指导如何调整
  • ¥35 西门子博图v16安装密钥提示CryptAcquireContext MS_DEF_PROV Error of containger opening
  • ¥15 mes系统扫码追溯功能
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部