Thanks
I made some progress, and this gets me most of the way there (only the
total count is wrong at present)
def get_queryset(self):
created_q = Q(created__gte=self.start_date) &
Q(created__lt=self.end_date)
completed_q = Q(completed__gte=self.start_date) &
Q(completed__lt=self.end_date)
opened_cnt = Count("type__name", filter=created_q)
completed_cnt = Count("type__name", filter=completed_q)
total_cnt = Count("type__name", filter=Q(created_q | completed_q))
qs = (
self.model.objects.filter(...)
.values("type__name")
.annotate(num_opened=opened_cnt)
.annotate(num_completed=completed_cnt)
.annotate(total=total_cnt)
)
return qs
On Wednesday, 9 November 2022 at 20:46:15 UTC julika...@gmail.com wrote:
> Post your model here and we do it
>
> On Wed, Nov 9, 2022, 7:37 PM Matthew Hegarty <mrhe...@gmail.com> wrote:
>
>> My question is about translating a SQL query to the ORM.
>>
>> I want to combine the output of two queries into one. The query is
>> counting records in the same table (Task) using two different fields.
>>
>> The query is:
>>
>> select q1.taskname, q1.count, q2.count, (q1.count + q2.count) as total
>> from (
>> select ptt.name as taskname, count(1) from task pt
>> inner join tasktype ptt on pt.type_id = ptt.id
>> where pt.created >= '2022-11-05T00:00:00+00:00'::timestamptz
>> group by ptt.name) q1
>> left join (
>> select ptt.name as taskname, count(1) from task pt
>> inner join tasktype ptt on pt.type_id = ptt.id
>> where pt.completed >= '2022-11-05T00:00:00+00:00'::timestamptz
>> group by ptt.name) q2
>> on q1.taskname = q2.taskname
>>
>> This gives an example output of
>>
>> taskname count1 count2 total
>> =======================================
>> 1st review 8 4 12
>> 2nd review 4 13 17
>>
>> This works fine using raw SQL, but is it possible to do this using the
>> ORM?
>>
>> Thanks
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django users" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to django-users...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/django-users/CAAcKVnmMWftvzceQJeBFYMKfBuOHZVgRGi%3D8BvoZts%3DaUY%2Bmyg%40mail.gmail.com
>>
>> <https://groups.google.com/d/msgid/django-users/CAAcKVnmMWftvzceQJeBFYMKfBuOHZVgRGi%3D8BvoZts%3DaUY%2Bmyg%40mail.gmail.com?utm_medium=email&utm_source=footer>
>> .
>>
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/2abbf194-71a1-4c45-8671-e2de3accefc1n%40googlegroups.com.
