Skip to content
Advertisement

Rails Nested SQL Queries

I have a database model Position(lat,lon) which holds latitudes and longitudes.

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.

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).

To make the query faster, I wrote the whole haversine_distance formula in the query:

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?

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:

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?

Thanks a lot!

Advertisement

Answer

Here’s how to make nested queries:

It makes the following request:

Note that only ids 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(...).

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement