Laravel关联模型中使用Haversine公式计算距离报错排查

我在实现两组坐标间的距离计算时，处理Laravel关联模型遇到了问题。现有user表和addresses表，addresses表通过user_id字段与user表关联，一个用户对应多个地址，经纬度存储在addresses表中。参考旧讨论编写了Haversine公式的查询代码，但执行时报错：Undefined function: 7 ERROR: operator does not exist: bigint = character varying...，能定位到关联部分有问题，但找不到具体原因，求解决方案。

当前代码：

$haversine = "(6371 * acos(cos(radians(cast(" . $coordinates->latitude . "as numeric))) 
    * cos(radians(latitude)) 
    * cos(radians(longitude) 
    - radians(cast(" . $coordinates->longitude . " as numeric))) 
    + sin(radians(cast(" . $coordinates->latitude . " as numeric))) 
    * sin(radians(latitude))))";


$users = $users->whereHas('addresses', function ($q) use ($radius, $haversine) {
    $q->selectRaw("{$haversine} AS distance")
        ->whereRaw("distance < ?", [$radius]);
    })->get();

错误原因分析 #

1. 字段类型不匹配：报错信息里的bigint = character varying说明addresses.user_id字段类型与users.id不一致（比如前者是字符串、后者是bigint），导致关联查询时类型转换失败。
2. PostgreSQL别名使用限制：PostgreSQL不允许在WHERE子句中直接引用SELECT定义的列别名，你代码里用distance < ?直接调用别名会触发逻辑错误。
3. SQL注入风险：直接拼接变量到SQL字符串中存在安全隐患。

解决方案 #

1. 修复关联字段类型不匹配 #

检查addresses表的user_id字段类型，确保和users.id一致（建议统一为bigint）。如果user_id是字符串类型，执行SQL修改：

ALTER TABLE addresses ALTER COLUMN user_id TYPE bigint USING user_id::bigint;

2. 修正Haversine公式查询写法 #

改用参数绑定避免SQL注入，同时调整查询逻辑适配PostgreSQL的别名限制：

仅筛选符合距离条件的用户

$users = $users->whereHas('addresses', function ($q) use ($coordinates, $radius) {
    $q->whereRaw("
        6371 * acos(
            cos(radians(?)) 
            * cos(radians(latitude)) 
            * cos(radians(longitude) - radians(?)) 
            + sin(radians(?)) 
            * sin(radians(latitude))
        ) < ?
    ", [
        (float)$coordinates->latitude,
        (float)$coordinates->longitude,
        (float)$coordinates->latitude,
        $radius
    ]);
})->get();

筛选用户并返回对应地址的距离值

$users = $users->with(['addresses' => function ($q) use ($coordinates) {
    $q->selectRaw("*, 
        6371 * acos(
            cos(radians(?)) 
            * cos(radians(latitude)) 
            * cos(radians(longitude) - radians(?)) 
            + sin(radians(?)) 
            * sin(radians(latitude))
        ) AS distance", [
        (float)$coordinates->latitude,
        (float)$coordinates->longitude,
        (float)$coordinates->latitude
    ]);
}])->whereHas('addresses', function ($q) use ($coordinates, $radius) {
    $q->whereRaw("
        6371 * acos(
            cos(radians(?)) 
            * cos(radians(latitude)) 
            * cos(radians(longitude) - radians(?)) 
            + sin(radians(?)) 
            * sin(radians(latitude))
        ) < ?
    ", [
        (float)$coordinates->latitude,
        (float)$coordinates->longitude,
        (float)$coordinates->latitude,
        $radius
    ]);
})->get();

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