PostgreSQL+Laravel开发附近车辆查询时出现distance列不存在错误

问题描述 #

开发基于经纬度和半径查找附近车辆（半径<10Km）的功能时，执行自定义查询报错，移除having()和orderBy()后查询可正常运行，当前使用PostgreSQL数据库。

原代码 #

$nearby = Vehicle::with('users','vehicle_feature.feature');

//NEARBY
if(request('latitude') && request('longitude')){
    $latitude = request('latitude');
    $longitude = request('longitude');
    $radius = request('radius') != null ? request('radius') : 10; // 10 km radius
    $nearby = $nearby
    ->selectRaw("*, (
        6371 * acos(
            cos(radians(?)) * cos(radians(parking_latitude)) * cos(radians(parking_longitude) -    radians(?)) 
           + sin(radians(?)) * sin(radians(parking_latitude))
        )
    ) AS distance", [$latitude, $longitude, $latitude])
    ->having('distance', '<=', $radius)
    ->whereIn('id', $result_rental_available)
    ->orderBy('distance')
    ->take(10)
    ->get();
        
}

错误信息 #

Illuminate\Database\QueryException: SQLSTATE[42703]: Undefined column: 7 ERROR:  column "distance" does not exist
LINE 6: ..., $47) and "vehicles"."deleted_at" is null having "distance"...
                                                             ^ (SQL: select *, (
            6371 * acos(
                cos(radians(55.67209151)) * cos(radians(parking_latitude)) * cos(radians(parking_longitude) - radians(12.56362796))
                + sin(radians(55.67209151)) * sin(radians(parking_latitude))
            )
        ) AS distance from "vehicles" where "user_id" <> a21209ca-24b5-4ea0-a701-a676de20bef7 and "id" in (01e4418c-f839-4881-b887-e476684d2b7d, 03973a8f-7b72-4012-baf5-fd1dc0a0c2ca, 050cfad7-6b8a-4d69-a39b-9a3228b3ea5c, 0d2fb0a8-f9dc-4317-91fe-3b3a9cad4071, 11575b01-8656-4eb8-bb70-48b579bca247, 151b583d-fb86-43ca-8054-2352f06f2f86, 22ec2c31-00eb-432f-a7e9-ed39313cd865, 283c6794-f700-4fb7-99f1-e1ddc2ca2cb4, 313c9742-a6aa-4f88-8f9e-8dd71a8f113b, 361e8840-4621-45b9-8553-cc35945f1d55, 3b57dd17-0674-41f8-8b9c-9fdfeec9adea, 3f24fa60-3e06-45a0-b267-dbbc8f1d382f, 42987e4a-2a99-4607-8d39-325fbd4dcacc, 46d65af0-1c75-45d8-bd06-f3946f976a16, 4cc0a1a7-dc23-4697-a8b3-3ba5dd0c7be6, 5e1f74fd-4036-4bb6-84dc-8810b765c54b, 685025e0-cf80-42be-90a3-63ea582cb3d2, 7131688f-7d38-4430-8f6f-089b15a4f90b, 71ed0ff9-bea5-4fdb-91e8-859566251190, 75245e7e-b58a-4e75-9f16-6994210820d3, 84eb2a33-0487-432f-9d5a-7860afab2b01, 88e80f28-d409-4fd3-84ca-625143985385, 8d80c918-0a31-4d94-9a97-c857a9f3b9f0, 969b5a66-9c1c-4ea5-99fd-b63dba167d7e, 97c797c6-0f3f-4781-85a4-6840b387c751, 99056d58-d712-4487-b173-e8b7be6027aa, 9f85574c-a092-4931-9a95-102017539d39, a2fea1df-3661-42aa-b894-a2c05cdd7658, a705d5e6-dfd3-4456-a83f-9ef5cc239866, b5d12ceb-9ce8-4a0f-8370-90eff4c2b206, bc8d484a-8367-41ea-969c-02c2967a954e, bca7214d-6ccb-42d0-9b9d-2cc162169f5f, d490bd21-efe1-482c-b3a4-1a839071622e, e14e2e30-9f2e-4256-9481-9232f0ad5209, e9dabfad-3a14-4b70-83f9-77b2bd003954, ea6fa50e-03fc-4637-9a47-d65c93eb015d, ee216e12-75bd-446b-85ba-5df4d2dfe757, f2051397-8616-49f4-9d77-fd113d09d57c, f4685109-2335-487c-810c-bf03974e8e3d, f69b00e6-2348-4966-b57f-e06accd21960, fbc11056-ff05-4f48-959d-65529cc1343b, fc3cbb24-6932-4fd6-9afb-c0db70bb847e, fe6df2db-0223-4425-8783-e6b1cab8085e) and "vehicles"."deleted_at" is null having "distance" <= 10 order by "distance" asc limit 10)

解决方案 #

错误原因 #

PostgreSQL中HAVING子句专为分组后的结果过滤设计，无GROUP BY时直接引用SELECT别名易出现解析异常；同时原查询中whereIn等条件的执行顺序与SELECT别名生成顺序冲突，导致数据库无法识别distance列。另外，用WHERE提前过滤数据比HAVING更高效。

修复方案1：使用whereRaw替代having（推荐） #

直接在WHERE子句中计算并过滤距离，避免别名解析问题，同时提升查询效率：

$nearby = Vehicle::with('users','vehicle_feature.feature');

//NEARBY
if(request('latitude') && request('longitude')){
    $latitude = request('latitude');
    $longitude = request('longitude');
    $radius = request('radius') ?? 10; // 简化空值判断
    
    $nearby = $nearby
        ->whereIn('id', $result_rental_available)
        ->selectRaw("*, (
            6371 * acos(
                cos(radians(?)) * cos(radians(parking_latitude)) * cos(radians(parking_longitude) - radians(?)) 
                + sin(radians(?)) * sin(radians(parking_latitude))
            )
        ) AS distance", [$latitude, $longitude, $latitude])
        ->whereRaw("(
            6371 * acos(
                cos(radians(?)) * cos(radians(parking_latitude)) * cos(radians(parking_longitude) - radians(?)) 
                + sin(radians(?)) * sin(radians(parking_latitude))
            )
        ) <= ?", [$latitude, $longitude, $latitude, $radius])
        ->orderBy('distance')
        ->take(10)
        ->get();
}

修复方案2：添加GROUP BY兼容HAVING #

如果坚持使用HAVING，需添加GROUP BY明确分组，让PostgreSQL正确识别SELECT别名：

$nearby = Vehicle::with('users','vehicle_feature.feature');

//NEARBY
if(request('latitude') && request('longitude')){
    $latitude = request('latitude');
    $longitude = request('longitude');
    $radius = request('radius') ?? 10;
    
    $nearby = $nearby
        ->whereIn('id', $result_rental_available)
        ->selectRaw("*, (
            6371 * acos(
                cos(radians(?)) * cos(radians(parking_latitude)) * cos(radians(parking_longitude) - radians(?)) 
                + sin(radians(?)) * sin(radians(parking_latitude))
            )
        ) AS distance", [$latitude, $longitude, $latitude])
        ->groupBy('vehicles.id') // 按车辆ID分组
        ->having('distance', '<=', $radius)
        ->orderBy('distance')
        ->take(10)
        ->get();
}

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