Rails Nested SQL Queries

Question

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:

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

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

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!

Do you know about Geocoder? You install the gem, add a line in your Position model, and then you can do stuff like Position.near([40.71, 100.23], 20). This gem is quite popular, so I'd say they do what you wanna do pretty well. (juts so you know, linking where clauses does not do what you want. I think it just overrides the previous ones). — Robin
– Robin, Commented Apr 13, 2012 at 19:47
Yeah, I know about Geocoder and I might just end up using it, but it seemed a bit big for what I want (I just want distances between objects). How much slower is an app that loads a whole Geocoder object compared to the barebones haversine_distance function I programmed? — gdiazc
– gdiazc, Commented Apr 13, 2012 at 20:01
@Robin It doesn't override the previous one, but it does append the conditions using AND to the current query, which isn't quite what the asker is looking for. — nzifnab
– nzifnab, Commented Apr 13, 2012 at 20:57

jdoe · Accepted Answer · 2012-04-13 20:53:57Z

36

Here's how to make nested queries:

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

It makes the following request:

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

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

edited Apr 13, 2012 at 20:53

answered Apr 13, 2012 at 20:39

jdoe

15.8k2 gold badges49 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zeal Over a year ago

Thanks for the post but I need little different help.. How to write query if I need to select all messages communicated between two user(say id1 & id2) and message have sender_id and receiver_id. So i need to select "all messages whose ((sender_id = id1 and reciver_id = id2) or (sender_id = id2 and reciver_id = id1))" in rails. I need to sort them out and paginate which can not be done if I make two separate query and then add them(I will have the desired result but pagination becomes difficult). So please help me out in writing single line query for this. Any help will be appreciated.

jdoe Over a year ago

@zeal Try github.com/activerecord-hackery/squeel for complicated queries.

Peter P. · Accepted Answer · 2019-07-16 03:21:41Z

1

I'd like to suggest an updated answer. In Rails v5.0.x, you can use select to use an attribute other than id while still doing a nested query.

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

answered Jul 16, 2019 at 3:21

Peter P.

3,5552 gold badges30 silver badges37 bronze badges

Collectives™ on Stack Overflow

Rails Nested SQL Queries

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related