Column "distance" does not exist when using Model.where

[kevinelliott]

I know this is documented as "this doesn't work yet, please help" on the README, but there is no Issue to track this. I figure if an issue exists, then people will know that others are actually wishing this was fixed. I know I need it!

PG::Error: ERROR: column "distance" does not exist

This occurs when you do something like:

stuff = Model.include(:photos).geo_scope(:origin => [latitude, longitude], :within => 100)
stuff = stuff.where(:published => true)

When you use where on the ARel chain, somehow DISTANCE is removed from the query, and no longer exists.

Please fix!

[jensb]

As far as I understand, this is a SQL issue. Basically, you cannot filter (WHERE / HAVING) by calculated columns, so you cannot say SELECT foo.*, some_math() AS distance FROM foo WHERE distance < 5; but you can say SELECT foo.*, some_math() AS distance FROM foo WHERE some_math() < 5. Also, ordering by calculated columns is allowed.

So, one possible solution would be to repeat the distance calculation mathematics in the WHERE clause. Of course, this calls for a stored procedure of some kind, otherwise the generated SQL will be rather messy.

Another alternative (when you don't have millions of rows) is to filter by Ruby after the fact using find { |x| x.distance < 10 } or something like that. But this is not useable in a scope, so it's more like a hack.

[jensb]

Another way of doing this would be something like
Model.geo_scope(:within => foo, :origin => whatever).where("bar.radius > #{Model.sphere_distance_sql(...)} AND ...").
That way, you are duplicating the complicated spherical math in the SQL request, but apart from possible(!) bad performance this should not hurt.