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

Question

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 ?

Answer 1

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