python - 如何在使用 ORM 的 django 中使用值左侧的注释（别名）字段作为分组依据（即值）子句的一部分？

Question

挑战通过使用 django ORM 利用带注释的字段来获得汇总结果，然后将具有该别名的值应用到值（分组依据）中，然后是其他注释以完成聚合字段

Sample data:

>>>
>>> models.Sale.objects.values().first()
{'id': 93, 'sales_order': 'SON4000006', 'transaction_date': '2020-05-04 00:00:00', 'customer_name': 'Customer No2', 'delivery_note': 'DN200006', 'vehicle_number': 'T101AAA', 'tax_invoice': 'TI900006', 'product_name': 'Pro Cememnt', 'quantity': '100', 'total_value': '50000', 'quantity2': None, 'total_value2': None, 'destination': 'Zambia', 'agent_id': None, 'created_at': datetime.datetime(2020, 5, 9, 6, 48, 26, 59719, tzinfo=<UTC>), 'updated_at': datetime.datetime(2020, 5, 9, 6, 48, 26, 59719, tzinfo=<UTC>)}
>>>
>>>
>>> models.Document.objects.values().first()
{'id': 27, 'ref_number': '2019 MATARE75/251', 'description': None, 'doc_type': 'Exit', 'file': 'docs/SKM_Sales20042214362_Msp5YxV.pdf', 'sale_id': 85, 'created_at': datetime.datetime(2020, 4, 25, 16, 11, 24, 847574, tzinfo=<UTC>), 'updated_at': datetime.datetime(2020, 4, 25, 16, 11, 24, 847574, tzinfo=<UTC>)}
>>>
>>>
>>>


>>>
>>> qs1=models.Sale.objects.annotate(docs_count=Count('docs'), complete=Case(When(docs_count=3, then=True), default=False, output_field=BooleanField())).values('destination', 'complete').annotate(count=Count('id'), qty=Sum('quantity'), value=Sum('total_value')).order_by('destination','complete')
>>>
>>>
>>>
>>> print(qs1.query)
SELECT 
    "sales_sale"."destination", 
    CASE WHEN COUNT("sales_document"."id") = 3 THEN True ELSE False END AS "complete", 
    COUNT("sales_sale"."id") AS "count", 
    SUM("sales_sale"."quantity") AS "qty", 
    SUM("sales_sale"."total_value") AS "value" 
FROM "sales_sale" 
    LEFT OUTER JOIN "sales_document" ON ("sales_sale"."id" = "sales_document"."sale_id") 
GROUP BY "sales_sale"."destination"
ORDER BY "sales_sale"."destination" ASC, "complete" ASC

预期输出为：

SELECT 
    "sales_sale"."destination", 
    CASE WHEN COUNT("sales_document"."id") = 3 THEN True ELSE False END AS "complete", 
    COUNT("sales_sale"."id") AS "count", 
    SUM("sales_sale"."quantity") AS "qty", 
    SUM("sales_sale"."total_value") AS "value" 
FROM "sales_sale" 
    LEFT OUTER JOIN "sales_document" ON ("sales_sale"."id" = "sales_document"."sale_id") 
GROUP BY "sales_sale"."destination", CASE WHEN COUNT("sales_document"."id") = 3 THEN True ELSE False END
ORDER BY "sales_sale"."destination" ASC, "complete" ASC

其他信息：

Django 3.0.2
Python 3.7
sqlite database

提前致谢

标签： pythondjango