django - 将 MySql 查询转换为 Django ORM 查询

Question

我在 MySql 中有一个查询，需要将其转换为 Django ORM。它涉及连接两个表，其中一个表有两个计数。我在 Django 中非常接近它，但我得到了重复的结果。这是查询:

SELECT au.id, 
       au.username, 
       COALESCE(orders_ct, 0) AS orders_ct, 
       COALESCE(clean_ct, 0)  AS clean_ct, 
       COALESCE(wash_ct, 0)   AS wash_ct 
FROM   auth_user AS au 
       LEFT OUTER JOIN 
           ( SELECT user_id,
                    Count(*)  AS orders_ct                    
             FROM   `order`
             GROUP  BY user_id
           ) AS o 
                    ON au.id = o.user_id 
       LEFT OUTER JOIN  
           ( SELECT user_id,
                    Count(CASE WHEN service = 'clean' THEN 1 
                          END)  AS clean_ct,
                    Count(CASE WHEN service = 'wash' THEN 1 
                          END)  AS wash_ct                            
             FROM   job
             GROUP  BY user_id
           ) AS j
                    ON au.id = j.user_id 
ORDER  BY au.id DESC 
LIMIT  100 ;

我当前的 Django 查询(返回不需要的重复项):

User.objects.annotate(
    orders_ct = Count( 'orders', distinct = True )
).annotate(
    clean_ct = Count( Case(
        When( job__service__exact = 'clean', then = 1 )
    ) )
).annotate(
    wash_ct = Count( Case(
        When( job__service__exact = 'wash', then = 1 )
    ) )
)

上面的 Django 代码生成以下查询，该查询很接近，但不正确:

SELECT DISTINCT `auth_user`.`id`, 
                `auth_user`.`username`, 
                Count(DISTINCT `order`.`id`) AS `orders_ct`, 
                Count(CASE 
                        WHEN `job`.`service` = 'clean' THEN 1 
                        ELSE NULL 
                      end)                   AS `clean_ct`, 
                Count(CASE 
                        WHEN `job`.`service` = 'wash' THEN 1 
                        ELSE NULL 
                      end)                   AS `wash_ct` 
FROM   `auth_user` 
       LEFT OUTER JOIN `order` 
                    ON ( `auth_user`.`id` = `order`.`user_id` ) 
       LEFT OUTER JOIN `job` 
                    ON ( `auth_user`.`id` = `job`.`user_id` ) 
GROUP  BY `auth_user`.`id` 
ORDER  BY `auth_user`.`id` DESC 
LIMIT  100 

我可能可以通过做一些事情来实现它raw sql subqueries但我想尽可能保持抽象。

Answer 1

基于this answer ，你可以写:

User.objects.annotate(
    orders_ct = Count( 'orders', distinct = True ),
    clean_ct = Count( Case(
        When( job__service__exact = 'clean', then = F('job__pk') )
    ), distinct = True ),
    wash_ct = Count( Case(
        When( job__service__exact = 'wash', then = F('job__pk') )
    ), distinct = True )
)

表(连接后):

user.id  order.id  job.id  job.service  your case/when  my case/when
1        1         1       wash         1               1
1        1         2       wash         1               2
1        1         3       clean        NULL            NULL
1        1         4       other        NULL            NULL
1        2         1       wash         1               1
1        2         2       wash         1               2
1        2         3       clean        NULL            NULL
1        2         4       other        NULL            NULL

wash_ct 的期望输出是 2。计算 my case/when 中的不同值，我们将得到 2。