Syntax error or access violation: 1140 Mixing of GROUP columns laravel

Question

I have written this query with pagination in it

$items = Item::select('items.*', 'sub_category_name', 'category_name', 'sub_category_slug', 'category_slug')
        ->join('sub_categories AS sc', 'sc.sc_id', 'items.sub_category_id')
        ->join('categories AS c', 'c.category_id', 'sc.category_id')
        ->where('items.is_active', '=', 1)
        ->where('sc.is_active', '=', 1)
        ->where('c.is_active', '=', 1)
        ->where('sc.sc_id', '=', $sub_category_id)
        ->paginate(1);

But it says

Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

But when I add ->groupBy('item_id'); it says

Syntax error or access violation: 1055 'books.items.item_name' isn't in GROUP BY

But when I do item_name in groupBy clause it says to groupBy the next column. Why?

What's the whole error message (including the query)? – Jonas Staudenmeir Apr 15 '18 at 22:00 — Jonas Staudenmeir, Apr 15 '18 at 22:00

score 26 · Answer 1 · answered Apr 16 '18 at 07:06

When you use aggregate functions like MIN(),MAX(),COUNT() AVG() you have to use Group BY

But in latest MYSQL u have to use all the columns in select as a group By too.

In your config/database.php turn off the strict mode.

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

'strict' => false after that you can use group by on a single column too.

score 0 · Answer 2 · answered Sep 24 '19 at 16:12

0

You can comment ONLY_FULL_GROUP_BY line in modes array

answered Sep 24 '19 at 16:12

Ganj Khani

1,229
15
20

Where is that? Could you explain a little bit more please? – Dharman Sep 24 '19 at 16:14
'modes' is one of 'mysql' options (such as 'driver'), if you have it, you should comment ONLY_FULL_GROUP_BY in modes array – Ganj Khani Sep 25 '19 at 05:21

Syntax error or access violation: 1140 Mixing of GROUP columns laravel

2 Answers2

Linked