How to Join Subqueries in Django ORM-CodePudding

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.

CodePudding user response：

You can do this with some fairly straightforward expressions, you just need to create the relevant time periods first using datetime and timedelta:

from datetime import datetime, timedelta
from django.db.models import Count

# get the first day of the current month
this_month = datetime.today().replace(day=1)
# get the start of the current 3 month period
current_3_months = (this_month - timedelta(days=80)).replace(day=1)
# get the 3 months prior to the current 3 month period
previous_3_months = (current_3_months - timedelta(days=80)).replace(day=1)

# an expression to count the no. of complaints created in the current 3 months
noise_count_current = Count(
    noisecomplaints__created_date__lt=this_month,
    noisecomplaints__created_date__gte=current_3_months,
)
# an expression to count the no. of complaints in the previous 3 months
noise_count_prev = Count(
    noisecomplaints__created_date__lt=current_3_months,
    noisecomplaints__created_date__gte=previous_3_months,
)

# get the NTA objects annotated by current, previous and diff 
# diff is the difference between the two expressions
nta = NTA.objects.annotate(
    noise_count_current=noise_count_current,
    noise_count_prev=noise_count_prev,
    noise_count_diff=noise_count_current-noise_count_prev
)

CodePudding user response：

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')
)