Laravel中count()结合havingRaw()出现数据库列不存在错误

问题描述 #

你编写的Eloquent查询在调用get()时能正常获取结果，但调用count()统计总数时，出现了Column not found: 1054 Unknown column 'distance' in 'having clause'的错误。相关查询代码如下：

$businessQuery = Business::select('businesses.id as business_id', 'users.id as user_id',\DB::raw('( 6371 * acos( cos( radians( '.$request->current_latitude.' ) ) * cos( radians( address_books.latitude ) ) * cos( radians( address_books.longitude ) - radians( '.$request->current_longitude.' ) ) + sin(radians('.$request->current_latitude.')) * sin(radians(address_books.latitude)) ) ) as `distance`'))
    ->join('users', 'businesses.user_id', '=', 'users.id')
    ->join('address_books', 'address_books.entity_id', '=', 'businesses.id');
$businessQuery = $businessQuery->where(array(
    'businesses.is_deleted'=>'N',
    'users.is_blocked'=>'N',
    'users.is_active'=>1,
    'address_books.entity_type'=>'M',
));
if($request->business_name != '') {
    $businessQuery = $businessQuery->where('businesses.business_name', 'like', '%' . $request->business_name . '%');
}
if($request->owner_name != '') {
    $businessQuery = $businessQuery->where('businesses.owner_name', 'like', '%' . $request->owner_name . '%');
}
if($request->service_men != '') {
    $businessQuery = $businessQuery->where('businesses.service_men', 'like', '%' . $request->service_men . '%');
}
if($request->service_women != '') {
    $businessQuery = $businessQuery->where('businesses.service_women', 'like', '%' . $request->service_women . '%');
}
if($request->service_kids != '') {
    $businessQuery = $businessQuery->where('businesses.service_kids', 'like', '%' . $request->service_kids . '%');
}
if($request->distance != '') {
    $businessQuery = $businessQuery->havingRaw('distance < '.$request->distance);
}

报错信息：

Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from businesses inner join users on businesses.user_id = users.id inner join address_books on address_books.entity_id = businesses.id where (businesses.is_deleted = N and users.is_blocked = N and users.is_active = 1 and address_books.entity_type = M) having distance < 200)

问题原因 #

核心问题出在Eloquent对count()方法的处理逻辑上：当你调用count()时，Eloquent会自动替换你原本定义的select子句为select count(*) as aggregate，这就导致你在select里定义的distance别名直接从查询中消失了。而你的having子句依赖这个distance别名，数据库自然找不到这个列，所以抛出了报错。

而get()方法会完整保留你定义的select子句，distance字段存在，having子句就能正常工作。

解决方案 #

这里有几种优雅的解决方式，你可以根据场景选择：

方式1：将距离计算直接移入havingRaw #

既然统计总数不需要返回distance字段，我们可以把原本在select里的距离计算公式直接放到havingRaw中，摆脱对distance别名的依赖，同时还能修复SQL注入风险：

if($request->distance != '') {
    $businessQuery = $businessQuery->havingRaw('( 6371 * acos( cos( radians( ? ) ) * cos( radians( address_books.latitude ) ) * cos( radians( address_books.longitude ) - radians( ? ) ) + sin(radians(?)) * sin(radians(address_books.latitude)) ) ) < ?', [
        $request->current_latitude,
        $request->current_longitude,
        $request->current_latitude,
        $request->distance
    ]);
}

方式2：使用子查询包裹原查询 #

把你编写的完整查询作为子查询，在外层统计总数，这样内层的distance别名会被保留，having子句也能正常生效：

if($request->distance != '') {
    $businessQuery = $businessQuery->havingRaw('distance < ?', [$request->distance]);
}

// 通过子查询统计总数
$totalCount = DB::table($businessQuery->getQuery(), 'sub')->count();

方式3：先获取结果再统计（小数据量适用） #

如果你的查询结果集数据量不大，可以先调用get()获取所有结果，再用集合的count()方法统计：

$businessResult = $businessQuery->get();
$totalCount = $businessResult->count();

这种方式简单直观，但如果数据量很大会占用较多内存，不推荐用于大数据场景。

额外优化建议 #

你原来的代码中直接把$request变量拼接到DB::raw里存在SQL注入风险，建议改成参数绑定的形式，比如定义select时：

$businessQuery = Business::select(
    'businesses.id as business_id',
    'users.id as user_id',
    DB::raw('( 6371 * acos( cos( radians( ? ) ) * cos( radians( address_books.latitude ) ) * cos( radians( address_books.longitude ) - radians( ? ) ) + sin(radians(?)) * sin(radians(address_books.latitude)) ) ) as `distance`'),
    [$request->current_latitude, $request->current_longitude, $request->current_latitude]
)
->join('users', 'businesses.user_id', '=', 'users.id')
->join('address_books', 'address_books.entity_id', '=', 'businesses.id');

这样能有效避免SQL注入问题，让代码更安全。

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