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