I am trying to get help to create django ORM queryset for the particular sql statement (you can also run it here) https://dbfiddle.uk/?rdbms=postgres_13&fiddle=31c9431d6753e2fdd8df37bbc1869e88
In particular to this question, I am more interested in the line that consist of:
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
Here is the whole sql if possible to convert to django ORM queryset
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
You can use the aggregate()
--(doc) method or (annotate()
--(doc) method) to get the result. For that, you must use the Min()
and Max()
database functions along with the filter
parameter.
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)),
)