Calling .last on ActiveRecord_Relation with by_distance generates bad sql

[samsondav]

Not sure if this is a bug in geokit-rails or ActiveRecord but I have a database query that uses by_nearby to sort locations by proximity. This works great if I append .first, .second etc to the query call.

It also gives expected behaviour if I use query[-1] to select the last record.

However, if I call query.last, it generates bad sql (presumably it tries to optimise by reversing the sort order, but this fails due to some complex math).

Here is the generated SQL:

>>> puts Friend.friends_closest_first(@user, @user.current_geolocation).to_sql
=> SELECT "friends".* FROM "friends" INNER JOIN "friendships" ON "friendships"."friend_id" = "friends"."id" INNER JOIN "geolocations" ON "geolocations"."id" = "friends"."geolocation_id" WHERE (friendships.user_id = 1)  ORDER BY
          (ACOS(least(1,COS(-0.5838126347921033)*COS(-1.2332496494591931)*COS(RADIANS(geolocations.latitude))*COS(RADIANS(geolocations.longitude))+
          COS(-0.5838126347921033)*SIN(-1.2332496494591931)*COS(RADIANS(geolocations.latitude))*SIN(RADIANS(geolocations.longitude))+
          SIN(-0.5838126347921033)*SIN(RADIANS(geolocations.latitude))))*3963.19)
          asc

This works fine:

>>>Friend.friends_closest_first(@user, @user.current_geolocation).first
=> first_record
>>>Friend.friends_closest_first(@user, @user.current_geolocation)[-1]
=> last_record

This fails:

>>>Friend.friends_closest_first(@user, @user.current_geolocation).last
=> ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "DESC"
LINE 1: ...(friendships.user_id = 1)  ORDER BY (ACOS(least(1 DESC, COS(...
                                                             ^
: SELECT  "friends".* FROM "friends" INNER JOIN "friendships" ON "friendships"."friend_id" = "friends"."id" INNER JOIN "geolocations" ON "geolocations"."id" = "friends"."geolocation_id" WHERE (friendships.user_id = 1)  ORDER BY (ACOS(least(1 DESC, COS(-0.5838126347921033)*COS(-1.2332496494591931)*COS(RADIANS(geolocations.latitude))*COS(RADIANS(geolocations.longitude))+
          COS(-0.5838126347921033)*SIN(-1.2332496494591931)*COS(RADIANS(geolocations.latitude))*SIN(RADIANS(geolocations.longitude))+
          SIN(-0.5838126347921033)*SIN(RADIANS(geolocations.latitude))))*3963.19)
          DESC LIMIT 1

Not a problem as I said, I can use the array[-1] selector as a workaround, but perhaps it could be possible to fix in geokit-rails so that last returns the correct value?

-S

[ryankopf]

I am closing all issues that are more than 6 years old now, as the gem has had significant updates and is likely people with these old issues are no longer having the issues. If you are currently still experiencing this issue or need help feel free to submit a new issue, thank you.