Django count RawQuerySet

Question

Hay, I'm using django 1.2 and i want to know how to count rows from a raw queryset(RawQuerySet).

The traditional .count() method doesn't work.

Heres my query

query = "SELECT *, ((ACOS(SIN(%s * PI() / 180) * SIN(lat * PI() / 180) + COS(%s * PI() / 180) * COS(lat * PI() / 180) * COS((%s - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM app_car WHERE price BETWEEN %s AND %s HAVING distance<=%s ORDER BY distance ASC"

cars = Car.objects.raw(query, [lat, lat, lon, min_price, max_price, miles])

return HttpResponse( cars )

And its returning

Car_Deferred_model_id_user_id object

Any ideas?

Answer 1

Use the 'len()' function. This would give:

query = "SELECT *, ((ACOS(SIN(%s * PI() / 180) * SIN(lat * PI() / 180) + COS(%s * PI() / 180) * COS(lat * PI() / 180) * COS((%s - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM app_car WHERE price BETWEEN %s AND %s HAVING distance<=%s ORDER BY distance ASC"

cars = Car.objects.raw(query, [lat, lat, lon, min_price, max_price, miles])

return HttpResponse(len(list(cars))

Aside: there's some useful information on the Django 1.2 Model.objects.raw() method at: http://djangoadvent.com/1.2/smoothing-curve/ [Looks like that site might have expired, but the Internet Archive has it at: http://web.archive.org/web/20110513122309/http://djangoadvent.com/1.2/smoothing-curve/ ]

Answer 2

Truth be told, if all you want is the total number of records in the RawQuerySet, then by all means you should avoid casting the RawQuerySet into a list.

Casting the RawQuerySet into a list will iterate through each record matching the query. This is potentially burdensome for the server. Use count() instead. This can be achieved by wrapping count() around the raw SQL you used to spawn the RawQuerySet.

I used this to solve the problem:

def add_len_protocol_to_raw_sql_query( query ):
    """
    Adds/Overrides a dynamic implementation of the length protocol to the definition of RawQuerySet for the remainder of this thread's lifespan
    """
    from django.db.models.query import RawQuerySet
    def __len__( self ):
        from django.db import connection
        sql = 'SELECT COUNT(*) FROM (' + query + ') B;'
        cursor = connection.cursor()
        cursor.execute( sql )
        row = cursor.fetchone()
        return row[ 0 ]
    setattr( RawQuerySet, '__len__', __len__ )
query = 'SELECT * FROM A_TABLE_OF_MINE'
add_len_protocol_to_raw_sql_query( query )

This makes a dynamic modification to RawQuerySet so that it responds to the len() protocol.

This is much better in terms of performance, thou there is potential for one drawback: If you use RawQuerySet more than once, then it would be desirable to discard the dynamic _len_ implementation.

Do any of you know if the _len_ method will be constrained by the caller's execution context? If using MOD_WSGI on Apache, does this means that all threads in the caller's process will share the modified definition?

Answer 3

Here is the improved solution that is based on user871977's:

from django.db import connection

def get_len(rawqueryset):
    def __len__(self):
        params = ["""'%s'""" % p for p in self.params]
        sql = 'SELECT COUNT(*) FROM (' + (rawqueryset.raw_query % tuple(params)) + ') B;'
        cursor = connection.cursor()
        cursor.execute(sql)
        row = cursor.fetchone()
        return row[0]
    return __len__

rawqueryset = .... # a RawQuerySet instance
setattr(type(rawqueryset), '__len__', get_len(rawqueryset))