MySQL子查询转Laravel查询报错：未知列‘cnt’求助

我来帮你搞定这个问题！你遇到的Unknown column 'cnt' in 'where clause'错误，本质是SQL的执行顺序导致的——WHERE子句会在SELECT子句之前执行，所以你在SELECT里定义的别名cnt，在WHERE里是无法直接引用的。咱们一步步来解决：

首先先回顾一下你的需求：把这段原生MySQL查询转成Laravel查询构造器：

select * from (
    SELECT *,(select count(*) from `comments` where parent_id=b._id) as cnt 
    FROM `comments` b
)x 
where ((x.type_user='xxxx' and (cnt>0 or x.is_starter=1)) or(type_user='user' and cnt>=0)) 
and deleted_at is null 
and parent_id is null 
order by created_at desc

你之前尝试的Laravel代码因为直接在WHERE里用了cnt别名，所以触发了错误。下面是几种可行的解决方案：

方案一：使用子查询包装（最贴近原生SQL逻辑） #

这种方式完全复刻原生SQL的思路，先把带cnt的结果查出来作为临时表，再在外层进行过滤，这样就能正常使用cnt别名了：

// 先构建子查询，获取包含cnt的所有评论数据
$subQuery = DB::table('comments')
    ->selectRaw('comments.*, (select count(*) from `comments` where parent_id = comments._id) as cnt');

// 将子查询作为临时表x，在外层执行过滤和排序
$res = DB::table($subQuery, 'x')
    ->whereNull('x.deleted_at')
    ->whereNull('x.parent_id')
    ->where(function ($query) {
        $query->where('x.type_user', 'xxxx')
            ->where(function ($query1) {
                $query1->where('x.cnt', '>', 0)
                    ->orWhere('x.is_starter', 1);
            });
    })
    ->orWhere(function ($query) {
        $query->where('x.type_user', 'user')
            ->where('x.cnt', '>=', 0);
    })
    ->orderBy('x.created_at', 'desc')
    ->get();

方案二：在WHERE中重复子查询逻辑（不推荐） #

虽然可以解决问题，但因为重复执行了子查询，性能会受影响，所以只作为备选：

$res = DB::table('comments')
    ->selectRaw('comments.*, (select count(*) from `comments` where parent_id = comments._id) as cnt')
    ->whereNull('deleted_at')
    ->whereNull('parent_id')
    ->where(function ($query) {
        $query->where('type_user', 'xxxx')
            ->where(function ($query1) {
                // 直接在where里写子查询逻辑，替代别名
                $query1->whereRaw('(select count(*) from `comments` where parent_id = comments._id) > 0')
                    ->orWhere('is_starter', 1);
            });
    })
    ->orWhere(function ($query) {
        $query->where('type_user', 'user')
            ->whereRaw('(select count(*) from `comments` where parent_id = comments._id) >= 0');
    })
    ->orderBy('created_at', 'desc')
    ->get();

方案三：用LEFT JOIN+聚合查询优化性能 #

如果你的数据量比较大，这种方式比子查询更高效，通过关联子评论并聚合计数来替代子查询：

$res = DB::table('comments as main')
    ->leftJoin('comments as child', 'child.parent_id', '=', 'main._id')
    ->selectRaw('main.*, COUNT(child._id) as cnt')
    ->whereNull('main.deleted_at')
    ->whereNull('main.parent_id')
    ->groupBy('main._id') // 注意：MySQL 5.7+开启ONLY_FULL_GROUP_BY时，需要group by所有main表的列，或者使用ANY_VALUE处理非聚合列
    ->where(function ($query) {
        $query->where('main.type_user', 'xxxx')
            ->where(function ($query1) {
                $query1->whereRaw('COUNT(child._id) > 0')
                    ->orWhere('main.is_starter', 1);
            });
    })
    ->orWhere(function ($query) {
        $query->where('main.type_user', 'user')
            ->whereRaw('COUNT(child._id) >= 0');
    })
    ->orderBy('main.created_at', 'desc')
    ->get();

推荐优先使用方案一，它和你的原生SQL逻辑完全一致，代码清晰且不容易出错。

内容的提问来源于stack exchange，提问作者user4206843