Django:分页器 + 原始 SQL 查询 [英] Django: Paginator + raw SQL query

问题描述

我在我的网站上到处使用 Django Paginator，甚至写了一个特殊的模板标签，以使其更方便.但现在我进入了一个状态，我需要进行一个复杂的自定义原始 SQL 查询，如果没有 LIMIT 将返回大约 100K 记录.

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.

如何在自定义查询中使用 Django Pagintor?

我的问题的简化示例:

我的模型:

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();

我在 Django ORM 中使用分页的方式:

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)

这样，Django 变得非常聪明，并在执行查询时将 LIMIT 添加到查询中.但是当我使用自定义管理器时:

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();

选择所有数据，然后才对python列表进行切片，非常慢.如何让自定义管理器的行为与内置管理器相似?

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?

推荐答案

看Paginator的源码，page() 函数 特别是，我认为这只是实现 slicing 在您身边，并将其转换为 SQL 查询中的相关 LIMIT 子句.您可能还需要添加一些缓存，但这开始看起来像 QuerySet，所以也许您可以做其他事情:

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:

• 您可以使用 CREATE VIEW myview AS [your query] 创建数据库 VIEW；
• 为该视图添加 Django 模型，使用 Meta: managed=False
• 像使用任何其他模型一样使用该模型，包括对其查询集进行切片 - 这意味着它非常适合与分页器一起使用

(供您参考 - 我已经使用这种方法很长时间了，即使是复杂的多对多关系与 VIEW 伪造 m2m 中间表.)

(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.)

这篇关于Django:分页器 + 原始 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！