laravel查询构建器上的左联接不存在A. *在GROUP BY中 [英] A.* isn't in GROUP BY with left join on laravel query builder

问题描述

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

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

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

但是当我使用select('A. *')选择所有A凝块时 我得到了错误

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:员工是我的数据库名称 A表上的列是

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

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

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

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

推荐答案

要解决此问题，您需要在选择列表和group by子句中指定必填列

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

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

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处理GROUP BY

根据文档

MySQL 5.7.5及更高版本实现对功能依赖性的检测.如果启用 ONLY_FULL_GROUP_BY SQL模式(这是默认设置)，MySQL拒绝选择列表，HAVING条件或ORDER BY列表引用未在GROUP BY子句中命名且在功能上不依赖于它们的未聚合列的查询. (在5.7.5之前，MySQL不会检测到功能依赖性，并且 ONLY_FULL_GROUP_BY 未启用

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

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