Django: Paginator + raw SQL query

Question

Hello!

I'm using Django Paginator everywhere on my website and even wrote a special template tag, to make it more convenient. But now I got to a state, where I need to make a complex custom raw SQL query, that without a LIMIT will return about 100K records.

How can I use Django Pagintor with custom query?

Simplified example of my problem:

My model:

class PersonManager(models.Manager):

    def complicated_list(self):

        from django.db import connection

        #Real query is much more complex        
        cursor.execute("""SELECT * FROM `myapp_person`""");  

        result_list = []

        for row in cursor.fetchall():
            result_list.append(row[0]); 

        return result_list


class Person(models.Model):
    name      = models.CharField(max_length=255);
    surname   = models.CharField(max_length=255);     
    age       = models.IntegerField(); 

    objects   = PersonManager();

The way I use pagintation with Django ORM:

all_objects = Person.objects.all();

paginator = Paginator(all_objects, 10);

try:
    page = int(request.GET.get('page', '1'))
except ValueError:
    page = 1

try:
    persons = paginator.page(page)
except (EmptyPage, InvalidPage):
    persons = paginator.page(paginator.num_pages)

This way, Django get very smart, and adds LIMIT to a query when executing it. But when I use custom manager:

all_objects = Person.objects.complicated_list();

all data is selected, and only then python list is sliced, which is VERY slow. How can I make my custom manager behave similar like built in one?

Answer 1

I don't know about Django 1.1 but if you can wait for 1.2 (which shouldn't be that long anymore) you can make use of objects.raw() as described in this article and in the development documentation.

Otherwise, if you query is not too complex, maybe using the extra clause is sufficient.

Answer 2

Looking at Paginator's source code, page() function in particular, I think that it's only matter of implementing slicing on your side, and translating that to relevant LIMIT clause in SQL query. You might also need to add some caching, but this starts to look like QuerySet, so maybe you can do something else:

• you can create database VIEW using CREATE VIEW myview AS [your query];
• add Django model for that VIEW, with Meta: managed=False
• use that model like any other model, including slicing its querysets - this means it's perfectly suitable for using with Paginator

(For your information - I've been using this approach for a long time now, even with complex many-to-many relationships with VIEWs faking m2m intermediate tables.)