Rails 嵌套 SQL 查询 [英] Rails Nested SQL Queries
问题描述
我有一个数据库模型 Position(lat,lon)
包含 latitudes
和 longitudes.
I have a database model Position(lat,lon)
which holds latitudes
and longitudes.
我有一个名为 show_close_by
的控制器操作,它接收以度为单位的位置 (my_lat, my_lon)
、容差(以公里为单位),并应返回位置列表公差范围内的数据库.
I have a controller action called show_close_by
which recieves a position in degrees (my_lat, my_lon)
, a tolerance (in kilometers) and should return the list of positions in the database that are within tolerance range.
为此,我使用 hasrsine_distance 公式计算两个坐标之间的距离(在地球表面上)(lat1, lon1, lat2, lon2)
.
For that, I use the haversine_distance formula which calculates the distance in kilometers (on the surface of the Earth) between two coordinates (lat1, lon1, lat2, lon2)
.
为了使查询更快,我在查询中编写了整个 haversine_distance
公式:
To make the query faster, I wrote the whole haversine_distance
formula in the query:
... 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
查询的细节无关紧要.我的疑问是:是否有必要为数据库中的每个位置计算这个巨大的函数?可以用更简单的函数过滤掉一些明显太远的位置吗?
The specifics of the query don't matter. My doubt is: is it necessary to calculate this huge function for EVERY position in the database? Can I filter out some positions that are clearly too far away with a simpler function?
好吧,我可以:使用嵌套的 SQL 查询,我可以查询数据库中大正方形"(在纬度/经度空间)内的位置,然后使用成本更高的三角函数过滤那些位置.类似于以下内容:
Well, I can: With a nested SQL query, I can query the database for positions that are within a large "square" (in lat/lon space), and then filter those with the more costly trigonometric function. Something like the following:
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)
是否进行嵌套查询?如果没有,怎么办?
Finally, my question: how can I implement this in Rails (without writing the whole query myself)? Does Positions.where(reasonable_upper_bound).where(costly_but_accurate_restriction)
make a nested query? If not, how?
非常感谢!
推荐答案
以下是进行嵌套查询的方法:
Here's how to make nested queries:
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)
请注意,仅 id
将从 products
表中获取.如果你想用另一种方式连接两个实体而这种魔法不合适,请使用 Product.select(:some_field).where(...)
.
Note that only id
s will be fetched from the products
table. If you are trying to make another way to connect two entities and this magic isn't suitable, use Product.select(:some_field).where(...)
.
这篇关于Rails 嵌套 SQL 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!