python - Django Annotated Query 仅计算反向关系中的最新查询
问题描述
问题概述
给定模型
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)
和 SQL 表:
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 |
+----+--------------+-----------+------------+
我想获取具有给定状态的候选人总数,但只计算最新的status_change。
换句话说,StatusChange 用于跟踪状态的历史记录,但在计算候选人的当前状态时只考虑最新的。
SQL 解决方案
使用 SQL,我可以使用 Group BY 和 COUNT 来实现它。(未经测试的 SQL)
SELECT
status.id as status_id
, status.name as status_name
, COUNT(*) as status_count
FROM
(
SELECT
status_id,
Max(created_at) AS latest_status_change
FROM
status_change
GROUP BY status_id
)
AS last_status_count
INNER JOIN
last_status_count AS status
ON (last_status_count.status_id = status.id)
GROUP BY status.name
ORDER BY status_count DESC;
last_status_count
+-----------+-------------+--------+
| status_id | status_name | count |
+-----------+-------------+--------+
| 1 | Review | 2 | # <= Does not include instance from candidate 1
| 2 | Accepted | 1 | # because status 2 is latest
| 3 | Rejected | 1 |
+-----------+-------------+--------+
尝试过的 Django 解决方案
我需要一个视图来返回每个状态及其相应的计数 - 例如[{ status_name: "Review", count: 2 }, ...]
我不确定如何构建这个查询集,而不是在 python 中提取所有记录和聚合。
我想我需要annotate()
并且可能Subquery
,但我无法将它们拼接在一起。
我得到的最接近的是这个,它计算每个状态的状态更改次数,但计算非最新更改。
queryset = Status.objects.all().annotate(case_count=Count("status_changes"))
我发现了很多关于聚合的 SO 问题,但我找不到关于聚合和注释“最新”的明确答案。
提前致谢。
解决方案
我们可以执行一个查询,首先过滤最后一个StatusChange
,Candidate
然后计算状态:
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)]
推荐阅读
- angular - Angular 12 ViewChild ElementRef
- python - 如果我在 for 语句中设置两个参数会发生什么?
- java - Avro 模式对象 - 递归
- c# - MS dotnet core 容器镜像拉取失败,错误:CTC1014
- c - 为什么switch语句在数字计数C程序中将整数计为空白字符?
- javascript - 反应原生导航:更改 headerRight flex
- azure-functions - 调用 context.done() 的最佳方式所需的建议
- java - 如何为具有私有访问说明符的内部类编写 Junit 测试用例?
- javascript - 使用 Blazor 加载 DOM 后关注输入字段
- android - android studio中的Pdf Viewer gradle问题