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

Question

我正在尝试寻求帮助为特定的 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

Answer 1

您可以使用 aggregate()--(doc)方法或(annotate()--(doc) 方法)来获取结果。为此，您必须使用 Min()和 Max()数据库函数以及 filter 参数。

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

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