获取所有客户发票的多个状态id的计数

Question

我的发票型号：

class Invoice(models.Model):
    client = models.ForeignKey(Clients)
    invoice_number = models.CharField(max_length=100)
    invoice_status = models.ForeignKey(Status, on_delete=models.CASCADE,null=True)

状态模型：

class Status(models.Model):
    invoice_status = models.CharField(max_length=50)

客户机型号：

class Clients(models.Model):
    name = models.CharField(max_length=50)
    address = models.TextField(null=True, blank=True)
    contact_person = models.CharField(max_length=50, null=True, blank=True)

状态将为

1.草案 2.已发送 3.已付

我尝试了多种方法，但都失败了

invoices = Invoice.objects.values("client").aggregate(Paid=Sum(Case(When(invoice_status__id=1, then=1),output_field=IntegerField())))

我想把所有客户的发票都算进去

预期结果：

客户id:1，草稿：4（计数），发送时间：5（计数），已支付：0（计数）

Answer 1

from collections import OrderedDict
from pprint import pprint
from django.db.models import Count

data = Invoice.objects.prefetch_related('invoice_status') \
    .values('client', 'invoice_status') \
    .annotate(count=Count('id')) \
    .order_by('client') \
    .values_list('client', 'invoice_status__invoice_status', 'count')
user_statuses = OrderedDict()
for client, status, count in data:
    statuses = user_statuses.get(client) or {}
    statuses[status] = count
    user_statuses[client] = statuses
pprint(user_statuses)