我正在尝试获取有关为特定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
你可以使用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)),
)