1055 Expression #3 of SELECT list is not in GROUP BY clause error in ManyToMany relationship laravel 5.3

Question

I have a Post Model like this:

class Post extends Model
{
        protected $primaryKey = 'post_id';
        public function tags ()
        {
            return $this->belongsToMany('App\Tag');
        }

}

and a Tag Model:

class Tag extends Model
{
        public function posts ()
        {
            return $this->belongsToMany('App\Post');
        }

        public function tagsCount ()
        {
            return $this->belongsToMany('App\Post')
                ->selectRaw('count(pt_id) as count')
                ->groupBy('tag_id');
        }

        public function getTagsCountAttribute()
        {
            if ( ! array_key_exists('tagsCount', $this->relations)) $this->load('tagsCount');

            $related = $this->getRelation('tagsCount')->first();

            return ($related) ? $related->count : 0;
        }
}

(pt_id column is primary key field in post_tag pivot table).

As you see ,there is a ManyToMany relation between Post and Tag Models.

For count related Tags of a specific Post ,I added tagsCount() and getTagsCountAttribute() methods to Tag Model.

Now suppose I want to get tags Count of a specific Post like this :

$post = Post::find($post_id)->get();
return $post->tagsCount

It worked for me in laravel 5.2 (and older versions) but after upgrading to laravel 5.3 , below error is shown:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'aids.post_tag.post_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select count(pt_id) as count, `post_tag`.`tag_id` as `pivot_tag_id`, `post_tag`.`post_id` as `pivot_post_id` from `posts` inner join `post_tag` on `posts`.`post_id` = `post_tag`.`post_id` where `post_tag`.`tag_id` in (145) and `posts`.`deleted_at` is null group by `post_tag`.`tag_id`)

What is Problem and how can I solve that ?

score 8 · Answer 1 · edited May 23 '17 at 12:24

8

This is related to mysql 5.7

Long story short, one solution is to try changing config/database.php from true to false:

'mysql' => [
    'strict' => false, //behave like 5.6
    //'strict' => true //behave like 5.7
],

For more info, see here: https://stackoverflow.com/a/39251942/2238694

edited May 23 '17 at 12:24

Community

1
1

answered Aug 31 '16 at 14:14

Ryan

5,959
2
25
24

I am using same mysql version for a laravel 5.2 and 5.3.but this problem is occured only in 5.3. – Ahmad Badpey Aug 31 '16 at 19:59

score 0 · Accepted Answer · answered Sep 06 '16 at 08:55

I do not try @Ryan, But with adding pt_id(primary key field in post_tag pivot table), problem solved :

public function tagsCount ()
        {
            return $this->belongsToMany('App\Post')
                ->selectRaw('count(pt_id) as count')
                ->groupBy(['tag_id', 'pt_id']);
        }

1055 Expression #3 of SELECT list is not in GROUP BY clause error in ManyToMany relationship laravel 5.3

2 Answers2