Laravel:在视图上分页查询MySQL错误1140 GROUP列的混合 [英] Laravel: Paginating a query on a view MySQL Error 1140 Mixing of GROUP columns

问题描述

我正在尝试对使用视图的查询进行简单的分页:

I am trying to do a simple pagination of a query that uses a view:

DB::table('vw_myview')->paginate(50)

并得到此错误:

SQLSTATE [42000]:语法错误或访问冲突:1140如果没有GROUP BY子句，则将GROUP列(MIN()，MAX()，COUNT()，...)与GROUP列混合使用是非法的(SQL:从vw_myview中选择count(*)作为汇总)

SQLSTATE[42000]: 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 (SQL: select count(*) as aggregate from vw_myview)

但是，当我手动运行查询时，它运行良好:

Yet when I manually run the query, it works just fine:

mysql> select count(*) as aggregate from vw_myview;
+-----------+
| aggregate |
+-----------+
|       776 |
+-----------+
1 row in set (0.20 sec)

搜索之后，我发现我只需要添加一个 GROUP BY 子句(就像错误所说的一样)，而我尝试这样做并没有运气:

After searching around I found that I should just have to add a GROUP BY clause (just like the error says) and I have tried doing that with no luck:

DB::table('vw_myview')->groupBy('column_on_view')->paginate(50)

SQLSTATE [42000]:语法错误或访问冲突:1055'database_name.table_used_by_view.column_on_table'不在GROUP BY中(SQL:按 column_on_view 从vw_myview组中选择count(*)作为汇总)

SQLSTATE[42000]: Syntax error or access violation: 1055 'database_name.table_used_by_view.column_on_table' isn't in GROUP BY (SQL: select count(*) as aggregate from vw_myview group by column_on_view)

好吧，让我们尝试按视图使用的表之一对列进行分组:

Okay lets try grouping by a column on one of the tables the view uses:

DB::table('vw_myview')->groupBy('table_used_by_view.column_on_table')->paginate(50)

SQLSTATE [42S22]:找不到列:1054'group statement'中的未知列'database_name.table_used_by_view.column_on_table'(SQL:选择count(*)作为来自vw_myview组的汇总，由database_name.table_used_by_view.column_on_table)

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'database_name.table_used_by_view.column_on_table' in 'group statement' (SQL: select count(*) as aggregate from vw_myview group by database_name.table_used_by_view.column_on_table)

那么，为什么当我手动运行查询时却能运行该查询，但通过Eloquent运行时却失败了?任何帮助将不胜感激.

So why does the query work when I run it manually but fails when run through Eloquent? Any help would be greatly appreciated.

推荐答案

您知道，我认为您的Mysql服务器已打开sql_mode = ONLY_FULL_GROUP_BY ...您可以检查一下...(从mysql中选择)@@ sql_mode;如果已设置，请将其关闭...并再次尝试查询...我认为它将通过... Mysql 5.7xxx现在默认情况下已将其打开...

You know, I am thinking your Mysql server has the sql_mode = ONLY_FULL_GROUP_BY turned on... Can you check this... (from mysql ... select @@sql_mode; If it is set, turn it off... and try your query again... I think it shall pass... Mysql 5.7xxx has this turned on by default now...

这篇关于Laravel:在视图上分页查询MySQL错误1140 GROUP列的混合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！