Warm tip: This article is reproduced from serverfault.com, please click

django orm queryset-如何执行sql查询MIN / MAX FILTER

(django orm queryset - how to perform sql query MIN/MAX FILTER)

发布于 2020-11-25 09:48:53

我正在尝试获取有关为特定sql语句创建Django ORM查询集的帮助(你也可以在此处运行它) https://dbfiddle.uk/?rdbms=postgres_13&fiddle=31c9431d6753e2fdd8df37bbc1869e88

特别是对于这个问题,我对以下内容更感兴趣:

MIN(created_at::time) FILTER (WHERE activity_type = 1) as min_time_in,
MAX(created_at::time) FILTER (WHERE activity_type = 2) as max_time_out

如果可能的话,这是整个SQL转换为Django ORM查询集

SELECT 
    created_at::date as created_date,
    company_id,
    employee_id,
    MIN(created_at::time) FILTER (WHERE activity_type = 1) as min_time_in,
    MAX(created_at::time) FILTER (WHERE activity_type = 2) as max_time_out
FROM
   timerecord
where date(created_at) = '2020-11-18' and employee_id = 1
GROUP BY created_date, company_id, employee_id
ORDER BY created_date, employee_id
Questioner
Axil
Viewed
11
JPG 2020-11-28 11:50:30

你可以使用aggregate()-(doc)方法或(annotate()---doc方法)获得结果。为此,必须将Min()Max()数据库函数与filter参数一起使用

from django.db.models import Min, Q, Max

agg_response = TimeRecord.objects.aggregate(
    min_time_in=Min("created_at", filter=Q(activity_type=1)),
    max_time_out=Max("created_at", filter=Q(activity_type=2)),
)