python - Django Annotated Query 以计算反向关系中使用的所有实体
问题描述
这个问题是这个 SO 问题的后续问题:Django Annotated Query to Count Only Latest from Reverse Relationship
鉴于这些模型:
class Candidate(BaseModel):
name = models.CharField(max_length=128)
class Status(BaseModel):
name = models.CharField(max_length=128)
class StatusChange(BaseModel):
candidate = models.ForeignKey("Candidate", related_name="status_changes")
status = models.ForeignKey("Status", related_name="status_changes")
created_at = models.DateTimeField(auto_now_add=True, blank=True)
由这些表表示:
candidates
+----+--------------+
| id | name |
+----+--------------+
| 1 | Beth |
| 2 | Mark |
| 3 | Mike |
| 4 | Ryan |
+----+--------------+
status
+----+--------------+
| id | name |
+----+--------------+
| 1 | Review |
| 2 | Accepted |
| 3 | Rejected |
+----+--------------+
status_change
+----+--------------+-----------+------------+
| id | candidate_id | status_id | created_at |
+----+--------------+-----------+------------+
| 1 | 1 | 1 | 03-01-2019 |
| 2 | 1 | 2 | 05-01-2019 |
| 4 | 2 | 1 | 01-01-2019 |
| 5 | 3 | 1 | 01-01-2019 |
| 6 | 4 | 3 | 01-01-2019 |
+----+--------------+-----------+------------+
我想获得每种状态类型的计数,但只包括每个候选人的最后状态:
last_status_count
+-----------+-------------+--------+
| status_id | status_name | count |
+-----------+-------------+--------+
| 1 | Review | 2 |
| 2 | Accepted | 1 |
| 3 | Rejected | 1 |
+-----------+-------------+--------+
我能够通过这个答案实现这一目标:
from django.db.models import Count, F, Max
Status.objects.filter(
status_changes__in=StatusChange.objects.annotate(
last=Max('candidate__status_changes__created_at')
).filter(
created_at=F('last')
)
).annotate(
nlast=Count('status_changes')
)
>>> [(q.name, q.nlast) for q in qs]
[('Review', 2), ('Accepted', 1), ('Rejected', 1)]
然而,问题是,如果任何状态更改都没有引用某个状态,那么它会从结果中省略。相反,我想把它算为零。例如,如果状态是
+----+--------------+
| id | name |
+----+--------------+
| 1 | Review |
| 2 | Accepted |
| 3 | Rejected |
| 4 | Banned |
+----+--------------+
我会得到:
+-----------+-------------+--------+
| status_id | status_name | count |
+-----------+-------------+--------+
| 1 | Review | 2 |
| 2 | Accepted | 1 |
| 3 | Rejected | 1 |
| 4 | Banned | 0 |
+-----------+-------------+--------+
>>> [(q.name, q.nlast) for q in qs]
[('Review', 2), ('Accepted', 1), ('Rejected', 1), ('Accepted 0)]
我试过的
我通过在 SQL 中进行外部联接解决了这个问题,但我不确定如何在 Djano 中实现这一点。我尝试创建一个所有计数都注释为零的查询集并将其合并,但它不起作用:
last_status_changes = Status.objects.filter(
status_changes__in=StatusChange.objects.annotate(
last=Max('candidate__status_changes__created_at')
).filter(
created_at=F('last')
)
).annotate(
nlast=Count('status_changes')
)
zero_query = (
Status.objects.all()
.annotate(nlast=Value(0, output_field=IntegerField()))
.exclude(pk__in=last_status_changes.values("id"))
)
>>> qs = last_status_changes | zero_query
>>> [(q.name, q.nlast) for q in qs]
[('Review', 3), ('Accepted', 1), ('Rejected', 1)]
# this would double count "Review" and include not only last but others
感谢任何帮助谢谢
更新 1
我能够通过使用右连接的原始查询来解决这个问题,但是使用 ORM 会很好
# Untested as I am using different model names in reality
SQL = """SELECT
Min(status.id) as id
, COUNT(latest_status_change.candidate_id) as status_count
FROM
(
SELECT
candidate_id,
Max(created_at) AS latest_date
FROM
api_status_change
GROUP BY candidate_id
)
AS latest_status_change
INNER JOIN api_candidates ON (latest_status_change.candidate_id = api_candidates.id)
INNER JOIN api_status_change ON
(
latest_status_change.candidate_id = api_candidates.id
AND
latest_status_change.latest_date = api_status_change.created_at
)
RIGHT JOIN api_status AS status ON (api_status_change.status_id = `status`.id)
GROUP BY status.name
;
"""
qs = Status.objects.raw(SQL)
>>> [(q.name, q.nlast) for q in qs]
[('Review', 2), ('Accepted', 1), ('Rejected', 1), ('Accepted 0)]
解决方案
这里唯一的问题是您正在State
通过现有状态更改过滤查询集并期望完全相反的结果。在您的情况下,解决方案是摆脱过时的过滤
last_status_changes = Status.objects.annotate(
nlast=Count('status_changes')
).order_by(
'-nlast'
)
另一种情况是,如果您想真正过滤您的更改(例如按日期)
changed_status_ids = Status.objects.filter(
status_changes__created_at__gte='2020-03-03'
).values_list(
'id',
flat=True
)
Status.objects.annotate(
c=Count('status_changes')
).annotate(
cnt=Case(
When(
id__in=changed_status_ids,
then=F('c')
),
output_field=models.IntegerField(),
default=0
)
).values(
'cnt',
'name'
).order_by(
'-cnt'
)
推荐阅读
- sql - 将字符连接并添加到 SQL 中的整数列
- c++ - union 给出了字符串数据类型的错误,但适用于 char 数据类型
- java - java.lang.NoClassDefFoundError: gherkin/IGherkinDialectProvider 在 Eclipse 中执行 testrunner 类时
- css - CSS Paged Media:无法实现所需的中断行为(break-after,break-before什么都不做)
- amazon-web-services - 请求 quicksight 嵌入 URL 时,“'userArn' 未能满足约束:在此区域中无法访问指定的资源”
- python - 如何计算数据框组中行的唯一组合?
- c++ - C++:实现向量和矩阵类的最佳结构
- php - 表单上传如何显示?当我运行这个文件时
- javascript - 如何获取所见即所得的价值并将其仅通过 Javascript 插入到 textarea 标签中?
- actionscript-3 - ActionScript3:在一个类中通过 addEventListener 读取文件,如何将数据传递给不同的类