Laravel 连表查询报语法错误,拿 sql 到数据库执行正常

laravel 连表查询报语法错误,拿 sql 到数据库执行正常,下面上代码#

        $model = new Curriculum;
        $data['data'] = $model->setTable('c')
            ->from('curriculums as c')
            ->select(DB::raw('c.*,GROUP_CONCAT(cp.position_id) AS position_ids'))
            ->where('c.c_id',$user_info['c_id'])
            ->where('c.is_del',0)
            ->leftJoin('curriculum_position as cp', 'c.id', '=', 'cp.curriculum_id')
//            ->when($keyword, function ($query) use ($keyword) {
//                return $query->where('c.name','like','%'.$keyword.'%');
//            })
            ->groupBy('c.id')
            ->orderBy('c.created_at','DESC')
            ->skip($skip)->take($limit)->get()->toArray();

laravel连表查询报语法错误,拿sql到数据库执行正常

报错信息:Illuminate\Database\QueryException: SQLSTATE [42000]: Syntax error or access violation: 1055 'zkw_teach.c.c_id' isn't in GROUP BY (SQL: select c.*,GROUP_CONCAT (cp.position_id) AS position_ids from curriculums as c left join curriculum_position as cp on c.id = cp.curriculum_id where c.c_id = 12 and c.is_del = 0 group by c.id order by c.created_at desc limit 10 offset 0)

复制此条 sql 到 navicat 中执行成功#

laravel连表查询报语法错误,拿sql到数据库执行正常

转变思路,使用原生 sql 写,结果依旧是报错,代码如下#

        $sql_pre = "SELECT c.*,GROUP_CONCAT(cp.position_id) as position_ids 
                    FROM curriculums AS c 
                    LEFT JOIN curriculum_position AS cp ON c.id = cp.curriculum_id
                    WHERE c.c_id = 12 AND c.is_del = 0 ";

//        if ($keyword){
//            $sql_pre .= " AND c.name LIKE \'%$keyword%\' ";
//        }

        $sql_suf = "GROUP BY c.id ORDER BY c.created_at DESC LIMIT $limit OFFSET $skip";

        $sql = $sql_pre.$sql_suf;
        $res = DB::select($sql);

报错信息依旧:Illuminate\Database\QueryException: SQLSTATE [42000]: Syntax error or access violation: 1055 'zkw_teach.c.c_id' isn't in GROUP BY (SQL: SELECT c.*,GROUP_CONCAT (cp.position_id) as position_ids FROM curriculums AS c
LEFT JOIN curriculum_position AS cp ON c.id = cp.curriculum_id
WHERE c.c_id = 12 AND c.is_del = 0 GROUP BY c.id ORDER BY c.created_at DESC LIMIT 10 OFFSET 0)

拿 sql 到 navicat 中执行也是正常的#

php
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
最佳答案

config 目录下的 database 文件里,把数据库配置 strict 的值设为 false 即可。

5年前 评论
private_wa (楼主) 5年前
讨论数量: 3

我能说,我已经有 10 几年没有写过两表以上的关联查询吗

5年前 评论
L学习不停 5年前
dongjw321 (作者) 5年前

config 目录下的 database 文件里,把数据库配置 strict 的值设为 false 即可。

5年前 评论
private_wa (楼主) 5年前

strict 即为严格模式 开启之后将会对 mysql 的 sql_mode 进行设置

file

关于 sql_mode 的更多了解可以参考下面这个文章就能解答你的疑惑了 https://www.cnblogs.com/fireporsche/p/8618...

5年前 评论
private_wa (楼主) 5年前