Combining two subquery counts using ORM

[Matthew Hegarty]

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

[kateregga julius]

Post your model here and we do it

--
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.

[Matthew Hegarty]

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

[Ross Meredith]

Last time I checked you can't use subquery inside the FROM clause.  If you can please let me know how because I had this a while back and got stuck.  I ended up using a package called django-cte.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/2abbf194-71a1-4c45-8671-e2de3accefc1n%40googlegroups.com.