使用活动记录查询接口查询N个最近的数据库条目

Question

在我的Rails项目中，我有一个名为"items"的表，其中每个项都有一个纬度和经度属性。当我为我的客户端检索项目时，我会获取当前用户的经度和纬度。使用这两组坐标，我想使用haversine函数，并用最小的haversine距离获取100个结果。

SQL应该是这样的：

SELECT
*,
haversine_distance(user_lat, user_long, item_lat, item_long) as distance
FROM
db.items
WHERE
item_lat < {user_lat} +1
AND item_lat > {user_lat}+1
AND ..
AND ..
ORDER BY distance DESC
LIMIT 100

但这就需要在查询中使用自定义函数，我不知道该怎么做。此外，关于查询的第7行和第8行，我正在尝试使用where子句来快速过滤某个合理距离内的结果集，这样我就不必将haversine应用于整个表。现在，我已经在一个名为DistanceService的单例中定义了该函数，但我认为我不能在查询中使用Ruby函数。

当我试图将函数haversine_distance添加到控制器并调用它时，如：

@items = Item.select("items.*, haversine_distance(geo_long, geo_lat,
#{@current_user.geo_long}, #{@current_user.geo_lat}) AS distance")
.where(status: 'available', ....)

我遇到了CCD_ 2。这让我认为应该首先通过一些迁移在SQL中定义函数，但我对SQL不太熟悉，也不知道如何通过迁移来实现这一点。

我还想知道是否有一个支持分页的解决方案，因为它有望最终成为我项目的需求。

谢谢。

Answer 1

在Rails中，您可以通过迁移定义数据库功能：

class AddHaversineDistanceFunction < ActiveRecord::Migration
def up
execute <<~SQL
DELIMITER $$
DROP FUNCTION IF EXISTS haversine_distance$$
CREATE FUNCTION haversine_distance(
lat1 FLOAT, lon1 FLOAT,
lat2 FLOAT, lon2 FLOAT
) RETURNS FLOAT
NO SQL DETERMINISTIC
COMMENT 'Returns the distance in km
between two known points of latitude and longitude'
BEGIN
RETURN DEGREES(ACOS(
COS(RADIANS(lat1)) *
COS(RADIANS(lat2)) *
COS(RADIANS(lon2) - RADIANS(lon1)) +
SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
)) * 111.045;
END$$
DELIMITER;
SQL
end
def down
execute "DROP FUNCTION IF EXISTS haversine_distance"
end
end

这里的实际功能改编自梅花岛传媒。

但您可能想看看地理编码器gem，它提供了这一功能，并使用了更好的公式。