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();
报错信息: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 中执行成功#
转变思路,使用原生 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)
推荐文章: