Django内置with模板标签为何导致重复数据库查询？

问题背景 #

Django官方文档提到with模板标签适合多次访问开销较大的操作（如数据库查询），但实际使用中，通过调试工具发现对with创建的变量执行操作时会触发重复查询。以下是简化后的代码：

模型定义 #

# models.py
class Contributor(models.Model):
    name = models.CharField(max_length=100)

class Contribution(models.Model):
    contributor = models.ForeignKey(
        Contributor, on_delete=models.RESTRICT, related_name='contributions'
    )
    class ROLE_CHOICES(models.TextChoices):
        A = 'AUTHOR', _('Author')
        C = 'COAUTHOR', _('Co-Author')
        E = 'EDITOR', _('Editor')
        V = 'VOICE', _('Voice Actor')
        S = 'AUDIO', _('Audio Engineer')

    role = models.CharField(max_length=10, choices=ROLE_CHOICES.choices)

class Scenario(models.Model):
    title = models.CharField(max_length=200)
    contributions = models.ManyToManyField(
        Contribution, related_name='contributions'
    )

视图代码 #

# views.py
class ScenarioDetailView(generic.DetailView):
    model = Scenario

自定义模板过滤器 #

# custom_tags.py
@register.filter
def only_role(scenario, test_role):
    return scenario.contributions.filter(role=test_role)

初始模板代码 #

# scenario_detail.html
{% load custom_tags %}

<dl>
{% with authors=scenario|only_role:"AUTHOR" %}
  {% if authors.count > 0 %}
    <dt>Author{{ authors.count|pluralize }}</dt>
    {% for contribution in authors %}
      <dd>{{ contribution.contributor.name }}</dd>
    {% endfor %}
  {% endif %}
{% endwith %}
</dl>

问题现象 #

调试工具显示两次重复的COUNT查询，加上一次获取数据的查询：

SELECT COUNT(*) AS "__count" FROM "scenario_contribution" INNER JOIN "scenario_scenario_contributions" ON ("scenario_contribution"."id" = "scenario_scenario_contributions"."contribution_id") WHERE ("scenario_scenario_contributions"."scenario_id" = 'demo' AND "scenario_contribution"."role" = 'AUTHOR')
2 similar queries.  Duplicated 2 times.

SELECT COUNT(*) AS "__count" FROM "scenario_contribution" INNER JOIN "scenario_scenario_contributions" ON ("scenario_contribution"."id" = "scenario_scenario_contributions"."contribution_id") WHERE ("scenario_scenario_contributions"."scenario_id" = 'demo' AND "scenario_contribution"."role" = 'AUTHOR')
2 similar queries.  Duplicated 2 times.

SELECT ••• FROM "scenario_contribution" INNER JOIN "scenario_scenario_contributions" ON ("scenario_contribution"."id" = "scenario_scenario_contributions"."contribution_id") INNER JOIN "scenario_contributor" ON ("scenario_contribution"."contributor_id" = "scenario_contributor"."id") LEFT OUTER JOIN "scenario_participant" ON ("scenario_contribution"."character_id" = "scenario_participant"."id") WHERE ("scenario_scenario_contributions"."scenario_id" = 'demo' AND "scenario_contribution"."role" = 'AUTHOR') ORDER BY "scenario_contributor"."last_name" ASC, "scenario_contributor"."first_name" ASC, "scenario_contribution"."role" ASC, "scenario_participant"."part_type" ASC, "scenario_participant"."designation" ASC

测试发现，将authors.count > 0改为{% if authors %}后，查询次数减少2次，说明重复查询确实来自多次调用count()方法。

原因分析 #

核心问题在于：only_role返回的是未执行的QuerySet对象。Django的QuerySet是惰性执行的，只有在实际需要数据时才会触发数据库查询。而count()方法不会缓存结果，每次调用都会单独发起一次SELECT COUNT(*)查询——即使你用with标签保存了QuerySet变量，两次调用authors.count仍然会触发两次重复的数据库请求，加上遍历QuerySet时的SELECT查询，总共三次查询。

优化方案 #

方案1：在过滤器中缓存查询结果（模板层优化） #

修改自定义过滤器，将QuerySet转换为列表并同时获取计数，这样只触发一次数据库查询：

# custom_tags.py
@register.filter
def get_role_data(scenario, test_role):
    qs = scenario.contributions.filter(role=test_role).select_related('contributor')
    items = list(qs)
    return {'items': items, 'count': len(items)}

模板中使用包装后的数据：

# scenario_detail.html
{% load custom_tags %}

<dl>
{% with role_data=scenario|get_role_data:"AUTHOR" %}
  {% if role_data.count > 0 %}
    <dt>Author{{ role_data.count|pluralize }}</dt>
    {% for contribution in role_data.items %}
      <dd>{{ contribution.contributor.name }}</dd>
    {% endfor %}
  {% endif %}
{% endwith %}
</dl>

方案2：视图层预取数据（推荐，符合最佳实践） #

在DetailView中提前预取所有贡献数据并按角色分组，模板直接使用预取好的内存数据，完全避免模板中的数据库查询：

# views.py
from django.views.generic import DetailView
from .models import Scenario, Contribution

class ScenarioDetailView(DetailView):
    model = Scenario

    def get_object(self, queryset=None):
        obj = super().get_object(queryset)
        # 预取关联的contributor，避免N+1查询
        contributions = obj.contributions.select_related('contributor').all()
        # 按角色分组
        obj.role_groups = {}
        for role_code, _ in Contribution.ROLE_CHOICES:
            obj.role_groups[role_code] = [c for c in contributions if c.role == role_code]
        return obj

模板中直接使用预取的分组数据：

# scenario_detail.html
<dl>
{% with authors=scenario.role_groups.AUTHOR %}
  {% if authors %}
    <dt>Author{{ authors|length|pluralize }}</dt>
    {% for contribution in authors %}
      <dd>{{ contribution.contributor.name }}</dd>
    {% endfor %}
  {% endif %}
{% endwith %}
</dl>

方案3：用exists()替代count()判断存在性 #

如果只需要判断是否有数据，不需要精确计数，可以用exists()——它会生成更高效的EXISTS查询，且多次调用会缓存结果：

{% with authors=scenario|only_role:"AUTHOR" %}
  {% if authors.exists %}
    <dt>Author{{ authors.count|pluralize }}</dt>
    {% for contribution in authors %}
      <dd>{{ contribution.contributor.name }}</dd>
    {% endfor %}
  {% endif %}
{% endwith %}

注：此方案仍会触发一次count()查询，适合不需要严格优化计数的场景。

内容的提问来源于stack exchange，提问作者DevinG