如何在Django ORM中连接子查询

Question

我是一个初学者Django，我已经在下面的问题上被困了一段时间了。

我想要实现的基本目标是，当客户端使用时间周期参数(比如3个月)发出GET列表api请求时，服务器将返回当前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.

Answer 1

你可以试试这个。

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

Answer 2

您可以使用一些非常简单的表达式来完成这一任务，您只需要首先使用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
)