mysql - 如何改进以下查询

Question

我需要获取给定提供商的所有用户的列表。一个用户可以有多个提供者，一个提供者也可以有多个用户。这是我到目前为止所拥有的:

我有下表:

class Provider(models.Model):
    provider = models.CharField(max_length=100, primary_key=True)

class UserProfile(models.Model):
    user = models.ForeignKey(User, primary_key=True)
    provider = models.ManyToManyField(Provider, db_column='provider')

在我看来:

providers = Provider.objects.order_by('provider')

在我的模板中:

{% for provider in providers %}
<tr>
    <td class="provider">
        {{ provider.provider }}
    </td>
    <td class="email">
        {% for profile in provider.userprofile_set.all %}
            {{ profile.user }}
        {% endfor %}    
    </td>
</tr>
{% endfor %}

这会产生大约 250 个查询，因为我正在迭代模板中的 Provider QuerySet。我将如何改进上述内容 - 有没有办法在这里使用 prefetch_lated 或 select_lated ？

Answer 1

使用prefetch_related() ; 选择
_lated() 不适用于 ManyToManyField:

providers = Provider.objects.order_by('provider').all().prefetch_related('userprofile_set')

来自文档:

Returns a QuerySet that will automatically retrieve, in a single batch, related objects for each of the specified lookups.

This has a similar purpose to select_related, in that both are designed to stop the deluge of database queries that is caused by accessing related objects, but the strategy is quite different.

...

prefetch_related, on the other hand, does a separate lookup for each relationship, and does the 'joining' in Python. This allows it to prefetch many-to-many and many-to-one objects, which cannot be done using select_related, in addition to the foreign key and one-to-one relationships that are supported by select_related.