Django foreign keys in extra() expression

Question

I'm trying to use the Django extra() method to filter all the objects in a certain radius, just like in this answer: http://stackoverflow.com/questions/19703975/django-sort-by-distance/26219292 but I'm having some problems with the 'gcd' expression as I have to reach the latitude and longitude through two foreign key relationships, instead of using direct model fields.

In particular, I have one Experience class:

class Experience(models.Model):   
      starting_place_geolocation = models.ForeignKey(GooglePlaceMixin, on_delete=models.CASCADE,
                                               related_name='experience_starting')
      visiting_place_geolocation = models.ForeignKey(GooglePlaceMixin, on_delete=models.CASCADE,
                                               related_name='experience_visiting')

with two foreign keys to the same GooglePlaceMixin class:

class GooglePlaceMixin(models.Model):
      latitude = models.DecimalField(max_digits=20, decimal_places=15)
      longitude = models.DecimalField(max_digits=20, decimal_places=15)
      ...

Here is my code to filter the Experience objects by starting place location:

    def search_by_proximity(self, experiences, latitude, longitude, proximity):

          gcd = """
                6371 * acos(
                cos(radians(%s)) * cos(radians(starting_place_geolocation__latitude))
                * cos(radians(starting_place_geolocation__longitude) - radians(%s)) +
                sin(radians(%s)) * sin(radians(starting_place_geolocation__latitude))
                          )
                          """
          gcd_lt = "{} < %s".format(gcd)

          return experiences \
              .extra(select={'distance': gcd},
               select_params=[latitude, longitude, latitude],
               where=[gcd_lt],
               params=[latitude, longitude, latitude, proximity],
               order_by=['distance'])

but when I try to call the foreign key object "strarting_place_geolocation__latitude" it returns this error:

column "starting_place_geolocation__latitude" does not exist

What should I do to reach the foreign key value? Thank you in advance

Alexandr Tatarinov · Answer 1 · 2018-05-14T20:38:38.573

When you are using extra (which should be avoided, as stated in documentation), you are actually writing raw SQL. As you probably know, to get value from ForeignKey you have to perform JOIN. When using Django ORM, it translates that fancy double underscores to correct JOIN clause. But the SQL can't. And you also cannot add JOIN manually. The correct way here is to stick with ORM and define some custom database functions for sin, cos, radians and so on. That's pretty easy.

class Sin(Func):
    function = 'SIN'

Then use it like this:

qs = experiences.annotate(distance=Cos(Radians(F('starting_place_geolocation__latitude') )) * ( some other expressions))

Note the fancy double underscores comes back again and works as expected You have got the idea.

Here is a full collection of mine if you like copy pasting from SO) https://gist.github.com/tatarinov1997/3af95331ef94c6d93227ce49af2211eb

P. S. You can also face the set output_field error. Then you have to wrap your whole distance expression into ExpressionWrapper and provide it an output_field=models.DecimalField() argument.

Django foreign keys in extra() expression

1 Answers1