Django带注释的查询对反向关系中使用的所有实体进行计数 [英] Django Annotated Query to Count all entities used in a Reverse Relationship
问题描述
此问题是此SO问题的后续问题： Django注释的查询仅计算反向关系中的最新查询
This question is a follow up question for this SO question : 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 |
+----+--------------+-----------+------------+
我想对每种身份类型进行计数，但只包括每个候选人的最后身份：
I wanted to get a count of each status type, but only include the last status for each candidate:
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)]
但是，问题是，如果任何状态更改都没有引用状态，则结果中将其省略。相反，我想将其视为零。
例如，如果状态为
The issue however, is if there is a status not reference by any status change, it's omitted from the result. Instead, I would like to count it as zero. For example, if the status were
+----+--------------+
| 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中实现。
我尝试创建一个查询集，并将所有计数都标注为零并将其合并，但是没有用：
What I tried
I solved this by doing an outer join in SQL but I am not sure how to achieve that in Djano. I tried creating a queryset with all counts annotated as zero and the merging it, but it did not work:
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
任何帮助都值得
谢谢
Any help is appreciated Thanks
我能够通过使用右联接的Raw Query解决此问题，但是使用ORM做到这一点非常好
I was able to solve this with a Raw Query using a right join, but would be great to do this using the 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)]
推荐答案
我使用以下查询集解决了该问题：
I solved it with the queryset below:
qs_last_status_changes = StatusChanges.objects
.annotate(
_last_change=models.Max("candidate__status_changes__create_at")
).filter(created_at=models.F("_last_change")
qs_status = Status.objects\
.annotate(count=models.Sum(
models.Case(
models.When(
status_changes__in=qs_last_status_changes,
then=models.Value(1)
),
output_field=models.IntegerField(),
default=0,
)
)
)
>>> [(k.name, k.count) for k in qs_status]
[('Review', 2), ('Accepted', 1), ('Rejected', 1), ('Accepted 0)]
谢谢Andrey Nelubin的建议
Thank you Andrey Nelubin for your suggestion
这篇关于Django带注释的查询对反向关系中使用的所有实体进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
