How to Join Subqueries in Django ORM

I’m a beginner Django and I have been stuck on the following problem for a while.

The basic thing I would like to achieve is that when client makes a GET list api request with a time period parameter (say 3 months) then the server would return an aggregation of the current 3 months data and also show an extra field comparing the difference with previous 3 months.

I have models as:

class NTA(models.Model):
    nta_code = models.CharField(max_length=30, unique=True)
    ...


class NoiseComplaints(models.Model):
    complaint_id = models.IntegerField(unique=True)
    created_date = models.DateTimeField()
    nta = models.ForeignKey(
        NTA, on_delete=models.CASCADE, to_field='nta_code')

    ...

The sample output I would like to get is something like:

[
        {
                "id": 1,
                "nta_code": "New York",
                "noise_count_current": 30, # this would be current 3m count of NoiseData
                "noise_count_prev": 20, # this would be prev 3m count of NoiseData
                "noise_count_diff": 10, # this would be prev 3m count of NoiseData
         }
...

The raw SQL query (in Postgres) is quite simple as below but I’m really struggling on how to make this work in Django.

WITH curr AS 
(
    SELECT "places_nta"."id", "places_nta"."nta_code",
    COUNT("places_noisecomplaints"."id") AS "noise_count_curr" 
    FROM "places_nta" 
    INNER JOIN "places_noisecomplaints" 
        ON ("places_nta"."nta_code" = "places_noisecomplaints"."nta_id") 
    WHERE "places_noisecomplaints"."created_date" 
        BETWEEN '2022-03-31 00:00:00+00:00' AND '2022-06-30 00:00:00+00:00' 
    GROUP BY "places_nta"."id"
),

prev AS 
(
    SELECT "places_nta"."id", "places_nta"."nta_code",
    COUNT("places_noisecomplaints"."id") AS "noise_count_prev" 
    FROM "places_nta" 
    INNER JOIN "places_noisecomplaints" 
        ON ("places_nta"."nta_code" = "places_noisecomplaints"."nta_id") 
    WHERE "places_noisecomplaints"."created_date" 
        BETWEEN '2022-01-01 00:00:00+00:00' AND '2022-03-31 00:00:00+00:00' 
    GROUP BY "places_nta"."id"
)

SELECT 
    curr.id, 
    curr.nta_code,
    noise_count_curr - COALESCE(noise_count_prev, 0) AS noise_count_diff,
    noise_count_curr, 
    noise_count_prev
FROM curr
LEFT JOIN prev
    ON curr.id = prev.id

What I have Tried

• I don’t think using the raw query (in the form I indicated above) as works in my case b/c I need it to handle filters (the GET request from client can have other query params from which server will handle additional filters)
• I tried to union the current and previous querysets and then groupby, but it seems that this is not supported:

qs1 = queryset.filter(noisecomplaints__created_date__range=["2022-03-31", "2022-06-30"]).annotate(
    noise_count=Count('noisecomplaints'),
    tag=models.Value("curr", output_field=models.CharField()),
)
qs2 = queryset.filter(noisecomplaints__created_date__range=["2022-01-01", "2022-03-31"]).annotate(
    noise_count=Count('noisecomplaints'),
    tag=models.Value("prev", output_field=models.CharField()),
)
qs_union = qs1.union(qs2, all=True)
qs_result = qs_union.values('id').annotate(
    noise_count_curr=Sum('noise_count', filter=Q(tag='curr')),
    noise_count_prev=Sum('noise_count', filter=Q(tag='prev')),
)

django.db.utils.NotSupportedError: Calling QuerySet.annotate() after union() is not supported.

Answer

You could try this one.

from django.db.models import Case, When, IntegerField, F, Count

q = queryset.values('id').annotate(
    noise_count_current= Count(Case(
        When(noisecomplaints__created_date__range=["2022-03-31", "2022-06-30"], then=1),
        output_field=IntegerField()
    )),
    noise_count_prev= Count(Case(
        When(noisecomplaints__created_date__range=["2022-01-01", "2022-03-31"], then=1),
        output_field=IntegerField()
    )),
    noise_count_diff = F('noise_count_current') - F('noise_count_prev')
)