Complex query in django

Question

Hay, i have a quite complex query i cant get working in django.

My model is called Car(), and i want to perform this query on it

query = "SELECT *, ((ACOS(SIN("+user_lat+" * PI() / 180) * SIN(lat * PI() / 180) + COS("+user_lat+" * PI() / 180) * COS(lat * PI() / 180) * COS(("+user_lon+" - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM app_car HAVING distance<='"+miles+"' ORDER BY distance ASC"

Any ideas?

Thanks

EDIT:

my view looks like this

def find_cars_within_miles_from_postcode(request, miles, postcode=0):

    # create cursor for RAW query
    cursor = connection.cursor()

    # Get lat and lon from google
    lat, lon = getLonLatFromPostcode(postcode)

    # Gen query
    query = "SELECT id, ((ACOS(SIN("+lat+" * PI() / 180) * SIN(lat * PI() / 180) + COS("+lat+" * PI() / 180) * COS(lat * PI() / 180) * COS(("+lon+" - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM app_car HAVING distance<='"+miles+"' ORDER BY distance ASC"

    # execute the query
    cursor.execute(query)

    # grab all the IDS form the sql result
    ids = [row[0] for row in cursor.fetchall()]

    # find cars from ids
    cars = Car.objects.filter(id__in=ids)

    # return the Cars with these IDS
    return HttpResponse( cars )

This returns my cars from x amount of miles, this works well. However the raw query returned how far they were from a certain location, i think the fieldname was 'distance'.

How can i return this field 'distance' with my car objects?

Answer 1

The Django ORM doesn't suppport HAVING; drop to DB-API for this.

Answer 2

User raw() method and that SQL query to get Cars objects.

Answer 3

Take a look at this SO post. It covers a geo lookup like you want.

Answer 4

You'll have a hard time returning the distance value inside a QerySet object (see my question on the same thing), at least until Django 1.2. The easiest thing is to just move your query into a model manager, but return a Python list instead of a queryset. You might also look at Python's geo library for handling these kinds of queries.