django - 使用 Django 中的多个字段进行计数(不同)

Question

在这里，我尝试将 Django rawsql 查询转换为 Django 查询集。

但问题是 - 如何在两列中实现不同？

SQL查询:

count(distinct ra, category) as order_type

Django 查询集 - order_type=Count('ra', 'category', distinct=True) - 这会导致错误。

queryset = Model.objects.raw("select id,category,count(category) 
as total_orders, count(distinct ra, category) as order_type, 
SUM(CASE WHEN service_status = 'success' THEN 1 ELSE 0 END) as total_success_order, 
SUM(CASE WHEN service_status = 'failed' THEN 1 ELSE 0 END) as total_failed_order 
from table 
group by ra;")


queryset = Model.objects.values('ra').annotate(category=F('category'),\
order_type=Count('ra', 'category', distinct=True),total_orders=Count('category'),\
total_success_order=Count('service_status', filter=Q(service_status='success')),\
total_failed_order=Count('service_status', filter=Q(service_status='failed'))).order_by()

正确的语法应该是什么？

数据-

category   ra   service_status
cat1       11      success
cat1       12      success
cat2       11      success
cat2       11      success
cat1       15      success
cat3       5       failed

ra 11 应该返回为 order_type = 2 (即，如果同一个 ra 属于两个不同的类别)

O/P-

Category  order type   total order   successful  failed
 cat1        1            10              5         5
 cat2        2            14              10       4
 cat3        1            9               9        0

Answer 1

Bit late, Hope this helps someone.

您可以使用 Django 数据库函数 Concat :

from django.db.models.functions import Concat

queryset = Model.objects.values('ra').annotate(order_type=Count(Concat('ra', 'category'), distinct=True))