如何在循环中消除django查询的低效率？

发布时间：2020-12-20 12:01:28 所属栏目：Python 来源：网络整理

导读：如何使以下代码更有效(例如,如何用查询替换循环)？ def get_question(datetime_now,questions_queryset,user): best_schedule = None best_question = None # HOW TO ELIMINATE THE FOLLOWING LOOP AND REPLACE WITH A QUERY? for question in questions_que

如何使以下代码更有效(例如,如何用查询替换循环)？

def get_question(datetime_now,questions_queryset,user):
    best_schedule = None
    best_question = None
    # HOW TO ELIMINATE THE FOLLOWING LOOP AND REPLACE WITH A QUERY?
    for question in questions_queryset:
        try:
            schedule = (Schedule.objects
                        .filter(question=question,user=user)
                        .latest(field_name='datetime_added')
        except ObjectDoesNotExist:
            schedule = None
        if (schedule and (schedule.date_show_next >= datetime_now) and
                ((not best_schedule) or
                 (schedule.datetime_added >= best_schedule.datetime_added))):
            best_schedule = schedule
            best_question = question

    return best_question

models.py

from django.contrib.auth.models import User
from django.db.models import DateTimeField,ForeignKey,Model,TextField

class Question(Model):
    question = TextField()

class Schedule(Model):
    datetime_added = DateTimeField(auto_now_add=True)
    datetime_show_next = DateTimeField(null=True,default=None)
    question = ForeignKey(Question)
    user = ForeignKey(User,null=True)

解决方法

您可以像在此答案 https://stackoverflow.com/a/43883397/3627387中使用 Subquery或使用 Prefetch https://stackoverflow.com/a/31237026/3627387

以下是使用Prefetch实现此目的的一种方法：

schedules_prefetch = Prefetch(
        'schedule_set',queryset=Schedule.objects.filter(user=user))
for question in questions_queryset.prefetch_related(schedules_prefetch):
    try:
        # using max here so it wouldn't do another DB hit
        schedule = max(question.schedule_set.all(),key=lambda x: x.datetime_added)
    except ValueError:
        schedule = None

这是一个使用Subquery的例子(它可能实际上不起作用,但会给你一般的想法)：

from django.db.models import OuterRef,Subquery
schedules = (Schedule.objects
             .filter(user=user,question=OuterRef('pk'))
             .order_by('datetime_added'))
questions_queryset = (questions_queryset
                    .annotate(latest_schedule=Subquery(schedules[:1])))
for question in questions_queryset:
    schedule = question.latest_schedule

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!