Django: Paginator + raw SQL query
- by Silver Light
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?
