对于 Django >= 1.8
Use 条件聚合 https://docs.djangoproject.com/en/1.8/ref/models/conditional-expressions/#conditional-aggregation:
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(),
))
)
解释:通过您的文章进行的正常查询将被注释为numviews
场地。该字段将被构造为 CASE/WHEN 表达式,由 Count 包装,对于读者匹配条件将返回 1,并且NULL
对于不符合标准的读者。 Count 将忽略空值并仅计算值。
您最近未查看过的文章将会得到零分,您可以使用它numviews
用于排序和过滤的字段。
PostgreSQL 的查询将是:
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"
如果我们只想跟踪唯一的查询,我们可以添加区分Count
,并使我们的When
子句返回值,我们想要区分。
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)
)
这将产生:
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"
对于 django
你可以只使用raw
用于执行由较新版本的 django 创建的 SQL 语句。显然,没有简单且优化的方法来查询该数据而不使用raw
(即使extra
需要注入时出现一些问题JOIN
条款)。
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"')