Django与经理相关的重复查询 [英] Django duplicate queries with manager related

问题描述

我正在优化我的ORM查询。我有两个应用， app1和 app2。一类 app2具有一类app1的外键，如下所示：

I'm working on optimizing my ORM queries. I have two apps, 'app1' and 'app2'. One class of 'app2' has a foreign key to a class of app1 as follows:

#app1/models.py
class C1App1(WithDateAndOwner):
  def get_c2_app2(self):
    res = self.c2app2_set.all()
    if res.count() > 0:
      return res[0]
    else:
      return None

#app2/models.py
class C2App2(WithDateAndOwner):
  c1app1 = models.ForeignKey("app1.C1App1")
  is_ok = models.BooleanField(default=False)

现在，我在管理页面中显示C1App1的所有实例的C2App2：

Now I display the C2App2 for all instances of C1App1 in the admin page:

#app1/admin.py
@admin.register(C1App1)
class C1App1Admin(admin.MyAdmin):
  list_display = ("get_c2_app2")
  list_select_related = ()
  list_prefetch_related = ("c2app2_set",)
  list_per_page = 10

prefetch_related 减少此查询：

SELECT ••• FROM `app2_c2app2` WHERE `app2_c2app2`.`c1app1_id` = 711
  Duplicated 19 times.

至：

SELECT ••• FROM `app2_c2app2` WHERE `app2_c2app2`.`c1app1_id` IN (704, 705, 706, 707, 708, 709, 710, 711, 702, 703) ORDER BY `app2_c2app2`.`id` DESC

这很好。现在，如果我想对C2App2的属性'is_ok'进行查询：

And it's fine. Now if I want to filter the query on C2App2's attribute 'is_ok':

#app1/models.py
class C1App1(WithDateAndOwner):
  def get_c2_app2(self):
    res = self.c2app2_set.filter(is_ok=False)
    if res.count() > 0:
      return res[0]
    else:
      return None

我仍然有这个预取的查询：

I still have this prefetched query:

SELECT ••• FROM `c2app2_set` WHERE `app2_c2app2`.`c1app1_id` IN (704, 705, 706, 707, 708, 709, 710, 711, 702, 703) ORDER BY `app2_c2app2`.`id` DESC

，但对于每个显示的C1App1实例都重复一个（10）。 ：

but with that one duplicated for each displayed instance of C1App1 (10). :

SELECT ••• FROM `app2_c2app2` WHERE (`app2_c2app2`.`c1app1_id` = 711 AND `app2_c2app2`.`is_ok` = 1)
  Duplicated 13 times.

实际上，查询还会针对显示的10个ID中的3个ID再次进行重复，从而得出这13个ID重复的查询。为了不重复这些查询，我该怎么办？似乎prefetch_related在这里不再有用。

Actually the query is also duplicated again for 3 ids out of the 10 displayed, which results in those 13 duplicated queries. What can I do in order to not duplicate those queries? It seems the prefetch_related doesn't help anymore here.

推荐答案

prefetch_related仅在使用 .all时有效。 （）。
如果应用任何其他转换，如 .filter（），将进行新的数据库查询。这是因为 prefetch_related 只是将所有相关实例缓存在列表中，因此Django无法在列表中执行 filter（）。要解决您的问题，您应该使用 Prefetch 对象。
您可以将 queryset 参数传递给它。因此，不要使用list_prefetch_related，而是在您的管理类中重写 get_queryset 方法。

prefetch_related only works when you use .all(). If you apply any other transformations like .filter(), the new DB query will be made. This is because prefetch_related simply caches ALL related instances in list, so Django cannot perform filter() on list. To solve your issue you should use Prefetch object. You can pass queryset parametr to it. So, instead of using list_prefetch_related, override get_queryset method in your admin class.

def get_queryset(*args, **kwargs):
     qs = super().get_queryset(*args, **kwargs)
     qs = qs.prefetch_related(Prefetch('c2app2_set', queryset=C2App2.objects.filter(is_ok=False)))
     return qs

p>

And

class C1App1(WithDateAndOwner):
   def get_c2_app2(self):
      res = self.c2app2_set.all()
      if res.count() > 0:
         return res[0]
      else:
         return None

这篇关于Django与经理相关的重复查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！