groupBy does not work properly for eloquent and query builder in laravel

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 ?

MYSQL5.7 has made some changes in this area check the migration note https://dev.mysql.com/doc/refman/5.7/en/faqs-migration.html You really should read ALL the error message that MYSQ provides, the clue is in the error message — RiggsFolly, Nov 14 '16 at 09:25
What do you want to select? Distinct user? I guess a user may have multiple fields/grades? — Olaf Dietsche, Nov 14 '16 at 09:25
@Olaf Dietsche,district educations for each user means select just one education for each user. — Ahmad Badpey, Nov 14 '16 at 09:59

score 2 · Answer 1 · answered Jul 01 '18 at 23:14

Try this ... In folder config => database.php make sure mysql strict is false, like this

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8',
    'collation' => 'utf8_general_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
],

if strict is true, make it false then clear config cash by run this command in cmd

php artisan config:clear

score 1 · Accepted Answer · edited May 23 '17 at 12:13

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

edited May 23 '17 at 12:13

Community

1
1

answered Nov 14 '16 at 09:25

jitendrapurohit

9,435
2
28
39

when I do not include PK in groupBy() , that error shown. Because of this I have to add PK to grouping. and then query runs but return all records and grouping does not work. – Ahmad Badpey Nov 14 '16 at 10:54

groupBy does not work properly for eloquent and query builder in laravel

2 Answers2