Django在一行上输出查询集的所有注释

Question

一个公司每月被分配许多任务。每个任务可以有三个选项：挂起、正在进行和已完成。例如：根据我对2021年1月期间的查询，我得到了正确的结果，但我无法在模板上正确列出这些结果，因为每个公司应该只在一行中显示一次，同时显示每个选项的计数。

型号.py

class Company(models.Model):
company = models.CharField(max_length=40)
class Period(models.Model):
period = models.CharField(max_length=7)
class Status(models.Model):
option = models.CharField(max_length=40)
class Task(models.Model):
company = models.ForeignKey(Company, on_delete=models.CASCADE, default=None, blank=True, null=True)
period = models.ForeignKey(Period, on_delete=models.CASCADE, default=None, blank=True, null=True)
status = models.ForeignKey(Status, on_delete=models.CASCADE, default=1)

视图.py

if request.method == 'POST':
if form.is_valid():
periods = Period.objects.get(period=form.cleaned_data['period'])
status = Task.objects.filter(period=periods).values('period', 'company__compnay', 'status__option', 'status').annotate(count_pending=Count('status', filter=Q(status=1))).annotate(count_inprogress=Count('status', filter=Q(status=2))).annotate(count_completed=Count('status', filter=Q(status=3))))
args = {'form': form, 'periods': periods, 'status': status}

模板

{% if status %}
<table class="table table-striped">
<thead>
<tr>
<th scope="col">Company</th>
<th scope="col">Tasks Pending</th>
<th scope="col">Tasks In Progress</th>
<th scope="col">Tasks Completed</th>
</tr>
</thead>
<tbody>
{% for stat in status %}
<tr>
<td>{{stat.company__company}}</td>
<td>{{stat.count_pending}}</td>
<td>{{stat.count_inprogress}}</td>
<td>{{stat.count_completed}}</td>
</tr>
{% endfor %}
</tbody>
</table>
{% endif %}

结果

Company       Tasks Pending         Tasks In Progress       Completed
Apple         0                     0                       1
Apple         0                     1                       0
Apple         2                     0                       0
Microsoft     0                     0                       2
Microsoft     0                     5                       0
Microsoft     5                     0                       2

预期结果

Company     Pending Tasks   In Progress Tasks   Completed Tasks
Apple       2               1                   1
Microsoft   5               5                   2

Answer 1

这一定是您想要的：

from django.db.models import Count, Case, When
Task.objects.filter(period=periods).values(
"company__company").annotate(
total_tasks=Count("company"),
count_pending=Count(Case(When(status=1, then=1))),
count_inprogress=Count(Case(When(status=2, then=1))),
count_completed=Count(Case(When(status=3, then=1))),
).order_by("company")