擅长:python、mysql、java
<p><strong>更新</strong></p>
<p>我提到的子查询方法现在在Django 1.11中通过<a href="https://docs.djangoproject.com/en/1.11/ref/models/expressions/#subquery-expressions" rel="noreferrer">subquery-expressions</a>支持。</p>
<pre><code>Event.objects.annotate(
num_paid_participants=Subquery(
Participant.objects.filter(
is_paid=True,
event=OuterRef('pk')
).values('event')
.annotate(cnt=Count('pk'))
.values('cnt'),
output_field=models.IntegerField()
)
)
</code></pre>
<p>比起聚合<em>(sum+case)</em>,我更喜欢这种方法,因为它应该更快、更容易优化<em>(使用适当的索引)</em>。</p>
<p>对于旧版本,也可以使用<a href="https://docs.djangoproject.com/en/1.8/ref/models/querysets/#extra" rel="noreferrer">^{<cd1>}</a>来实现</p>
<pre><code>Event.objects.extra(select={'num_paid_participants': "\
SELECT COUNT(*) \
FROM `myapp_participant` \
WHERE `myapp_participant`.`is_paid` = 1 AND \
`myapp_participant`.`event_id` = `myapp_event`.`id`"
})
</code></pre>