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 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
– RiggsFolly, Commented Nov 14, 2016 at 9:25
What do you want to select? Distinct user? I guess a user may have multiple fields/grades? — Olaf Dietsche
– Olaf Dietsche, Commented Nov 14, 2016 at 9:25
@Olaf Dietsche,district educations for each user means select just one education for each user. — Ahmad Badpey
– Ahmad Badpey, Commented Nov 14, 2016 at 9:59

AnasSafi · Accepted Answer · 2018-07-01 23:14:53Z

2

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

answered Jul 1, 2018 at 23:14

AnasSafi

6,3941 gold badge42 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:13:56Z

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, 2017 at 12:13

CommunityBot

11 silver badge

answered Nov 14, 2016 at 9:25

jitendrapurohit

9,7032 gold badges31 silver badges42 bronze badges

1 Comment

Ahmad Badpey Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related