A.* 不在 GROUP BY 中，在 Laravel 查询构建器上使用左连接 [英] A.* isn't in GROUP BY with left join on laravel query builder

问题描述

 $search_alls=DB::table('a16s as A')-> 选择('A.id')//->select('A.*')->addSelect(DB::raw('SUM(CASE WHEN B.approve = 1 ELSE 0 END) as Yshow'))->leftjoin('a16s_likes as B', function($join) {$join->on('A.id', '=', 'B.p_id');})-> groupBy('A.id')->get();

当我使用上面的 select('A.id') 时效果很好.

但是当我使用 select('A.*') 来选择所有 A cloumn我收到错误

SQLSTATE[42000]:语法错误或访问冲突:1055 'employee.A.name' 不在 GROUP BY 中

PS:employee 是我的数据库名A表上的列是

id 名称 ....1 约翰2 玛丽3 苏珊

如何通过leftjoin选择所有列?A.id 列与 B.p_id 列是一对多的关系.

解决方案

要解决此问题，您需要在 select list 和 group by 子句中指定所需的列

$search_alls=DB::table('a16s as A')->select('A.id','A.name')->addSelect(DB::raw('SUM(CASE WHEN B.approve = 1 ELSE 0 END) as Yshow'))->leftjoin('a16s_likes as B', function($join) {$join->on('A.id', '=', 'B.p_id');})-> groupBy('A.id')-> groupBy('A.name');->get();

<块引用>

根据较新的版本，mysql 5.7 不允许查询的选择列表、HAVING 条件或 ORDER BY 列表引用未在 GROUP BY 子句中命名的非聚合列

12.19.3 MySQL 处理分组依据

<小时>

根据文档

<块引用>

MySQL 5.7.5 及更高版本实现了功能依赖检测.如果启用了 ONLY_FULL_GROUP_BY SQL 模式(默认情况下)，MySQL 拒绝选择列表、HAVING 条件或 ORDER BY 列表引用非聚合列的查询，这些列既不在 GROUP BY 子句中命名，也不在功能上依赖于它们.(5.7.5之前，MySQL不检测函数依赖和ONLY_FULL_GROUP_BY 默认不启用

   $search_alls=
    DB::table('a16s as A')
    ->select('A.id')
    // ->select('A.*')
    ->addSelect(DB::raw('SUM(CASE WHEN B.approve = 1 ELSE 0 END) as Yshow'))
    ->leftjoin('a16s_likes as B', function($join) {
        $join->on('A.id', '=', 'B.p_id');
        })
    ->groupBy('A.id')
    ->get();

when I use above select('A.id') is work well.

But when I use select('A.*') to select all A cloumn I got the error

SQLSTATE[42000]: Syntax error or access violation: 1055 'employee.A.name' isn't in GROUP BY 

PS:employee is my DB name The column on A table is

id name ....
1  john
2  mary
3  susan

How can I select all column by the leftjoin? the column A.id is one to many relationship to the B.p_id column.

解决方案

To fix this issue you need to specify required columns in select list and group by clause

$search_alls=DB::table('a16s as A')
                ->select('A.id','A.name')
                ->addSelect(DB::raw('SUM(CASE WHEN B.approve = 1 ELSE 0 END) as Yshow'))
                ->leftjoin('a16s_likes as B', function($join) {
                    $join->on('A.id', '=', 'B.p_id');
                })
                ->groupBy('A.id')
                ->groupBy('A.name');
    ->get();

As per newer release mysql 5.7 does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause

12.19.3 MySQL Handling of GROUP BY

---

As per docs

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default

这篇关于A.* 不在 GROUP BY 中，在 Laravel 查询构建器上使用左连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！