Re: Return SQL calculation within queryset

Please read the following bits of documentation:

https://docs.djangoproject.com/en/dev/topics/db/sql/#performing-raw-sql-queries

https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.extra 

Cheers,

AT

On Tue, Jul 24, 2012 at 9:03 PM, jondbaker <jonathandavidbaker@gmail.com> wrote:

I've implemented the spherical law of cosines to aid in proximity-based searching. Everything works correctly, but I'm a bit stumped as to how I can return the calculated distance for a record given that it's not a field in the model. In the Model Manager below, row[1] represents the dynamically calculated distance, but I need to find a way to "attach" each calculation to it's corresponding record so that I can print the distance in a template.

class LocationManager(models.Manager):

    '''

    '''

    def nearby_locations(self, latitude, longitude, radius):

        '''

        '''

        cursor = connection.cursor()

        if settings.DATABASES['default']['ENGINE'] == 'django.db.backends.sqlite3':

            # sqlite doesn't natively support math functions, so add them

            connection.connection.create_function('acos', 1, math.acos)

            connection.connection.create_function('cos', 1, math.cos)

            connection.connection.create_function('radians', 1, math.radians)

            connection.connection.create_function('sin', 1, math.sin)

        sql = """SELECT id, (3959 * acos(cos(radians(%f)) *

              cos(radians(latitude)) * cos(radians(longitude) - radians(%f)) +

              sin(radians(%f)) * sin(radians(latitude))))

              AS distance FROM locations_location

              GROUP BY id HAVING distance < %d

              ORDER BY distance ASC""" % (latitude, longitude, latitude,

                      int(radius))

        cursor.execute(sql)

        data = [(row[0], row[1]) for row in cursor.fetchall()]

        ids = [i[0] for i in data]

        return self.filter(id__in=ids)

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To view this discussion on the web visit https://groups.google.com/d/msg/django-users/-/pfZSZB1nLVIJ.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.