<h3>对于django>;=1.8</h3>
<p>使用<a href="https://docs.djangoproject.com/en/1.8/ref/models/conditional-expressions/#conditional-aggregation" rel="noreferrer">Conditional Aggregation</a>:</p>
<pre><code>from django.db.models import Count, Case, When, IntegerField
Article.objects.annotate(
numviews=Count(Case(
When(readership__what_time__lt=treshold, then=1),
output_field=IntegerField(),
))
)
</code></pre>
<p><strong>说明:</strong>
通过文章的常规查询将使用<code>numviews</code>字段进行注释。该字段将被构造为CASE/WHEN表达式,由Count包装,对于readership匹配条件返回1,对于readership not matching条件返回<code>NULL</code>。Count将忽略nulls和Count-only值。</p>
<p>对于最近没有查看过的文章,您将得到0,您可以使用该<code>numviews</code>字段进行排序和筛选。</p>
<p>PostgreSQL的查询将是:</p>
<pre><code>SELECT
"app_article"."id",
"app_article"."author",
"app_article"."published",
COUNT(
CASE WHEN "app_readership"."what_time" < 2015-11-18 11:04:00.000000+01:00 THEN 1
ELSE NULL END
) as "numviews"
FROM "app_article" LEFT OUTER JOIN "app_readership"
ON ("app_article"."id" = "app_readership"."which_article_id")
GROUP BY "app_article"."id", "app_article"."author", "app_article"."published"
</code></pre>
<p>如果我们只想跟踪唯一的查询,我们可以在<code>Count</code>中添加distinction,并使我们的<code>When</code>子句返回值,我们希望distinct on。</p>
<pre><code>from django.db.models import Count, Case, When, CharField, F
Article.objects.annotate(
numviews=Count(Case(
When(readership__what_time__lt=treshold, then=F('readership__reader')), # it can be also `readership__reader_id`, it doesn't matter
output_field=CharField(),
), distinct=True)
)
</code></pre>
<p>这将产生:</p>
<pre><code>SELECT
"app_article"."id",
"app_article"."author",
"app_article"."published",
COUNT(
DISTINCT CASE WHEN "app_readership"."what_time" < 2015-11-18 11:04:00.000000+01:00 THEN "app_readership"."reader_id"
ELSE NULL END
) as "numviews"
FROM "app_article" LEFT OUTER JOIN "app_readership"
ON ("app_article"."id" = "app_readership"."which_article_id")
GROUP BY "app_article"."id", "app_article"."author", "app_article"."published"
</code></pre>
<h3>对于django<;1.8和PostgreSQL</h3>
<p>您可以使用<code>raw</code>来执行由django的较新版本创建的SQL语句。显然,如果不使用<code>raw</code>(即使使用<code>extra</code>注入必需的<code>JOIN</code>子句,也没有简单且优化的方法来查询该数据。</p>
<pre><code>Articles.objects.raw('SELECT'
' "app_article"."id",'
' "app_article"."author",'
' "app_article"."published",'
' COUNT('
' DISTINCT CASE WHEN "app_readership"."what_time" < 2015-11-18 11:04:00.000000+01:00 THEN "app_readership"."reader_id"'
' ELSE NULL END'
' ) as "numviews"'
'FROM "app_article" LEFT OUTER JOIN "app_readership"'
' ON ("app_article"."id" = "app_readership"."which_article_id")'
'GROUP BY "app_article"."id", "app_article"."author", "app_article"."published"')
</code></pre>