如何使用Django ORM选择多对一到多数百个查询？

问题描述

我的数据库具有以下模式：

My database has the following schema:

class Product(models.Model):
    pass

class Tag(models.Model):
    product = models.ForeignKey(Product)
    attr1 = models.CharField()
    attr2 = models.CharField()
    attr3 = models.CharField()

class AlternatePartNumber(models.Model):
    product = models.ForeignKey(Product)

换句话说，一个产品有许多标签 s，而产品有许多 AlternatePartNumber s。 标签是产品的属性集合。

In other words, a Product has many Tags, and a Product has many AlternatePartNumbers. Tags are a collection of attributes of the Product.

鉴于标签中的三个属性，我想选择与之匹配的相关联的产品 s（可能更多比一个），以及每个产品的所有 AlternatePartNumber 。

Given the three attributes in a Tag, I want to select the associated Products that match (could be more than one), as well as all of the AlternatePartNumbers of each product.

目前我这样做： / p>

Currently I do this:

# views.py
results = Tag.objects.
    filter(attr1=attr1).
    filter(attr2=attr2).
    filter(attr3=attr3)

# a template
{% for result in results %}
    {% for alternate in result.product.alternatepartnumber_set.all %}
        {{ alternate.property }}
    {% endfor %}
{% endfor %}

这可以运行数千个查询，具体取决于匹配的数量。有没有一个很好的方法来优化这个？我尝试使用 Tag.objects.select_related（）。filter ... ，并帮助了一些，但没有足够的帮助。

This can run thousands of queries, depending on the number of matches. Is there a good way to optimize this? I tried using Tag.objects.select_related().filter... and that helped some, but it didn't help enough.

推荐答案

Product和AlternatePartNumber之间的关系是一种反向ForeignKey关系，因此 select_related（）将无法正常工作。您需要 prefetch_related（），它比 select_related（）稍微有点小，但可以处理多对一关系。

The relationship between Product and AlternatePartNumber is a reverse ForeignKey relationship, so select_related() won't work. You need prefetch_related(), which is a little less aggressive than select_related() but can handle many-to-one relationships.

我之前没有使用prefetch_related（），但如果我正在阅读，您需要像 Tag.objects.prefetch_related（'product__alternatepartnumber_set'）过滤器。 .. 。如果不起作用，请在AlternatePartNumber模型上指定一个related_name，并使用它而不是 alternatepartnumber_set 。

I haven't used prefetch_related() myself before but if I'm reading the documentation correctly, you need something like Tag.objects.prefetch_related('product__alternatepartnumber_set').filter.... If that doesn't work, specify a related_name on the AlternatePartNumber model and use that instead of alternatepartnumber_set.

这篇关于如何使用Django ORM选择多对一到多数百个查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！