语法错误或访问冲突:1140 GROUP列的混合laravel [英] Syntax error or access violation: 1140 Mixing of GROUP columns laravel

问题描述

我用分页符编写了这个查询

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);

但是它说

语法错误或访问冲突:1140如果没有GROUP BY子句，则将GROUP列(MIN()，MAX()，COUNT()，...)与GROUP列混合使用是非法的

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

但是当我添加->groupBy('item_id');时，它会显示

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

语法错误或访问冲突:1055'books.items.item_name'不在GROUP BY中

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

但是当我在groupBy子句中执行item_name时，它说要在下一列进行groupBy.为什么?

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

推荐答案

使用诸如MIN(),MAX(),COUNT() AVG()之类的聚合函数时，必须使用Group BY

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

但是在最新的MYSQL中，您也必须将select中的所有列也作为一个分组使用.

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

在您的config/database.php中，关闭严格模式.

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之后，您也可以在单个列上使用分组依据.

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

这篇关于语法错误或访问冲突:1140 GROUP列的混合laravel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！