将复杂的MySQL查询空间计算转换为高级查询

Question

我正在查询我的数据库，该数据库在给定的时间内查找地图上距离用户最近的点。经度/经度存储在名为coords点列中。表在user表上进行了左连接，因此我可以获得相应的名称。

该查询在MySql工作台中运行良好。然而，我正在努力将其转换为基于雄辩的查询。我不喜欢我们的RAW，因为它不提供雄辩的基于链式方法所提供的保护。

以别名为us.id的列名为例，我得到了类似于找不到"us“和"id”列的错误。在其他各种类型中。

下面是包含等价模型应该采用的变量的MySQL查询。

// Some variables 
SET @orig_latitude = 1.234;
SET @orig_longitude = 5.678;
SET @radius = 500;

// Constants
SET @earth_size_size_in_miles = 3956;

//My query
SELECT us.name, ls.user_id, ls.coords, ls.lastseen_at AS lasttime,
    (  @earth_size_size_in_miles
                        * ACOS( COS(RADIANS(@orig_latitude))
                        * COS(RADIANS(ST_Y(coords)))
                        * COS(RADIANS(ST_X(coords))
                        - RADIANS(@orig_longitude) )
                        + SIN(RADIANS(@orig_latitude))
                        * SIN(RADIANS(ST_Y(coords)))
        )
    ) AS distance
FROM
    last_seens ls
LEFT JOIN
    users us ON ls.user_id = us.id
WHERE
     ls.lastseen_at >= NOW() - INTERVAL 15 MINUTE
HAVING
    DISTANCE < @radius
ORDER BY
    DISTANCE;

这只是我的一个失败的尝试，至少让它工作，但什么都不会工作。

$lat = 1.234;
$lon = 5.678;

$radius = 500;
$earth_size_size_in_miles = 3956;

$results = DB::table('last_seens AS ls')
    ->select('us.name', 'ls.user_id', 'ls.lastseen_at AS lasttime', DB::raw('( ?
                                            * ACOS( COS(RADIANS(?))
                                            * COS(RADIANS(ST_Y(coords)))
                                            * COS(RADIANS(ST_X(coords))
                                            - RADIANS(?) )
                                            + SIN(RADIANS(?))
                                            * SIN(RADIANS(ST_Y(coords)))
                            )
                        ) AS distance ', [$earth_size_size_in_miles, $lat, $lon, $lat]))
    ->leftJoin('users us', 'user_id ', '=', 'us.id')
    ->where(' ls.lastseen_at', '>=', 'NOW() - INTERVAL 15 MINUTE')
    ->havingRaw('DISTANCE < ?', [$radius])
    ->orderBy('DISTANCE')
    ->get();

users表是Laravel附带的开箱即用的标准users表。

我的LastSeens迁移：

Schema::create('last_seens', function (Blueprint $table) {
                $table->bigIncrements('id')->unsigned();
                $table->integer('user_id')->unsigned();
                $table->point('coords');
                $table->timestamp('lastseen_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));
            });

DB::statement("CREATE SPATIAL INDEX sx_last_seen_coords ON last_seens(coords);");
DB::statement("CREATE INDEX ts_last_seen_timestamp ON last_seens(lastseen_at);");
DB::statement("CREATE INDEX ix_last_seen_user_id ON last_seens(user_id);");
DB::statement("ALTER TABLE last_seens ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;");

有什么帮助吗？

Answer 1

你不能..。目前还没有。

虽然您可以在ORM中进行简单的聚合，但如果不运行子查询，就不能进行这种计算，因为子查询会破坏聚合。

我可能建议将此工作卸载到您的数据库，然后将其作为视图拉回。