如何获取注释Count的主键

Question

你好 stackoverflow 社区，我的问题是关于 django annotate 的。

基本上我想做的是从两个不同表中的两个不同字段中查找具有相同值的重复值。

这是我的 models.py

class Order(models.Model):
    id_order = models.AutoField(primary_key=True)

class OrderDelivery(models.Model):
    order = models.ForeignKey(Order, on_delete=models.SET_NULL, null=True, blank=True)
    delivery_address = models.TextField()

class OrderPickup(models.Model):
    order = models.ForeignKey(Order, on_delete=models.SET_NULL, null=True, blank=True)
    pickup_date = models.DateField(blank=True, null=True)

这是我当前的代码：

dup_job = Order.objects.filter(
        orderpickup__pickup_date__range=(start_date, end_date)
    ).values(
        'orderdelivery__delivery_address',
        'orderpickup__pickup_date',
    ).annotate(
        duplicated=Count('orderdelivery__delivery_address')
    ).filter(
        duplicated__gt=1
    )

根据我所拥有的，我得到这样的结果（出于隐私目的，省略了delivery_address）：

{'orderdelivery__delivery_address': '118A', 'orderpickup__pickup_date': datetime.date(2022, 3, 9), 'duplicated': 2}
{'orderdelivery__delivery_address': '11', 'orderpickup__pickup_date': datetime.date(2022, 3, 2), 'duplicated': 6}
{'orderdelivery__delivery_address': '11 A ', 'orderpickup__pickup_date': datetime.date(2022, 3, 3), 'duplicated': 5}
{'orderdelivery__delivery_address': '21', 'orderpickup__pickup_date': datetime.date(2022, 3, 10), 'duplicated': 3}
{'orderdelivery__delivery_address': '642', 'orderpickup__pickup_date': datetime.date(2022, 3, 7), 'duplicated': 2}
{'orderdelivery__delivery_address': '642', 'orderpickup__pickup_date': datetime.date(2022, 3, 8), 'duplicated': 2}
{'orderdelivery__delivery_address': 'N/A,5', 'orderpickup__pickup_date': datetime.date(2022, 3, 8), 'duplicated': 19}

有没有办法获取那些“重复”的 id_order？ 我尝试将 id_order 包含在 .values() 中，但输出不准确，因为注释是按 id_order 而不是 Delivery_address 分组的。

先感谢您

Answer 1

您可以使用 获取最小（或最大）的项目Min [Django-doc]（或Max）聚合：

from django.db.models import Min

dup_job = Order.objects.filter(
    orderpickup__pickup_date__range=(start_date, end_date)
).values(
    'orderdelivery__delivery_address',
    'orderpickup__pickup_date',
).annotate(
    min_id_order=Min('id_order')
    duplicated=Count('orderdelivery__delivery_address')
).filter(
    duplicated__gt=1
)

或对于postgresql，您可以使用 ArrayAgg [Django-doc] 生成列表：

# PostgreSQL only

from django.contrib.postgres.aggregates import ArrayAgg

dup_job = Order.objects.filter(
    orderpickup__pickup_date__range=(start_date, end_date)
).values(
    'orderdelivery__delivery_address',
    'orderpickup__pickup_date',
).annotate(
    min_id_order=ArrayAgg('id_order')
    duplicated=Count('orderdelivery__delivery_address')
).filter(
    duplicated__gt=1
)