Rails嵌套SQL查询

Question

我的数据库模型是Position(lat,lon)，它保存了纬度和经度。

我有一个名为show_close_by的控制器动作，它接收一个以度为单位的位置 (my_lat, my_lon) ，一个公差（以千米为单位），并应返回数据库中在公差范围内的位置列表。

为此，我使用haversine_distance公式计算两个坐标 (lat1，lon1，lat2，lon2) 之间在地球表面上的距离（以公里为单位）。

为了使查询更快，我在查询中编写了整个 haversine_distance 公式：

... WHERE 2*6371*asin(sqrt( power( sin( (:lat2-latitude)*pi()/(180*2) ) ,2) + cos(latitude*pi()/180)*cos(:lat2*pi()/180)*power(sin( (:lon2-longitude)*pi()/(180*2) ),2) )) < tolerance

查询语句的具体内容并不重要。我的疑问是：是否需要对数据库中的每个位置都计算这个巨大的函数？我能否使用一个简单一些的函数来过滤掉那些明显距离太远的位置呢？

嗯，我可以：通过一个嵌套的SQL查询，我可以查询在一个大的“正方形”（在纬度/经度空间中）内的位置，然后过滤掉那些需要更多成本的三角函数。像下面这样：

SELECT * FROM ( SELECT * FROM Positions WHERE lat-latitude < some_reasonable_upper_bound AND lon-longitude < same_upper_bound ) WHERE costly_haversine_distance < tolerance

最后，我的问题是：我如何在Rails中实现这一点（而不必自己编写整个查询）？Positions.where(reasonable_upper_bound).where(costly_but_accurate_restriction)是否会生成嵌套查询？如果不是，那应该怎么做？

非常感谢！

- gdiazc

---

你知道Geocoder吗？你可以安装这个gem，在你的Position模型中添加一行代码，然后就可以做像Position.near([40.71, 100.23], 20)这样的事情了。这个gem非常受欢迎，所以我想他们能很好地完成你想做的事情。（只是让你知道，链接where子句并不能实现你想要的效果。我认为它只是覆盖了之前的子句）。 - Robin

是的，我知道Geocoder，我可能最终会使用它，但对于我的需求来说它似乎有点大（我只想要对象之间的距离）。相比我编写的最简haversine_distance函数，加载整个Geocoder对象的应用程序会慢多少？ - gdiazc

@Robin 它不会覆盖之前的条件，但它会使用 AND 将条件追加到当前查询中，这并不完全符合提问者的要求。 - nzifnab

Answer 1

以下是如何进行嵌套查询：

LineItem.where(product_id: Product.where(price: 50))

它进行以下请求：

SELECT "line_items".* FROM "line_items" 
WHERE "line_items"."product_id" IN 
(SELECT "products"."id" FROM "products" WHERE "products"."price" = 50)

请注意，仅从products表中获取id。如果您想要创建另一种连接两个实体的方式，并且这种方法不适用，请使用Product.select(:some_field).where(...)。

- jdoe

---

谢谢您的帖子，但我需要不同的帮助。如果我需要选择两个用户之间通信的所有消息（比如id1和id2），并且消息具有sender_id和receiver_id，该如何编写查询语句呢？因此，我需要选择“所有消息，其((sender_id = id1 and receiver_id = id2) or (sender_id = id2 and receiver_id = id1))”。在Rails中，我需要对它们进行排序和分页，如果我进行两个单独的查询然后将它们添加起来（我将得到所需的结果，但分页变得困难）。因此，请帮我编写一行查询语句。非常感谢您的任何帮助。 - zeal

@zeal 尝试使用 https://github.com/activerecord-hackery/squeel 处理复杂查询。 - jdoe

Answer 2

我想提出一个更新的答案。在Rails v5.0.x中，您可以使用select来使用属性而不是id进行嵌套查询。

LineItem.where(product_id: Order.where(price: 50).select(:product_id))

- Peter P.