Как соединить подзапросы в Django ORM

Я начинающий Django и я застрял на следующей проблеме в течение некоторого времени.

Основное, чего я хотел бы достичь, это то, что когда клиент делает запрос GET list api с параметром периода времени (скажем, 3 месяца), то сервер возвращает агрегированные данные за текущие 3 месяца, а также показывает дополнительное поле, сравнивающее разницу с предыдущими 3 месяцами

У меня есть такие модели как:

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

    ...

Образец вывода, который я хотел бы получить, выглядит примерно так:

[
        {
                "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
         }
...

Необработанный SQL-запрос (в Postgres) довольно прост, как показано ниже, но я действительно пытаюсь понять, как заставить это работать в 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

Что я пробовал

  • Я не думаю, что использование сырого запроса (в форме, которую я указал выше) подходит в моем случае, так как мне нужно, чтобы он обрабатывал фильтры (GET запрос от клиента может иметь другие параметры запроса, из которых сервер будет обрабатывать дополнительные фильтры)
  • Я пытался объединить текущий и предыдущий наборы запросов, а затем выполнить groupby, но похоже, что это не поддерживается:
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.

Хотелось бы получить помощь по этому вопросу - заранее большое спасибо!

Вы можете сделать это с помощью довольно простых выражений, вам просто нужно сначала создать соответствующие временные периоды, используя datetime и 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
)

Вы можете попробовать этот вариант.

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')
)
Вернуться на верх