sql - db函数中的Django ORM过滤返回多个重复行

Question

我必须列出所有客户并在一个 sql 查询中注释他们的贷款和付款总额。我用下一段代码试了一下:

clients = Client.objects.all().order_by('fullname').annotate(
    loans_total=ArrayAgg(
        'sales__loan',
        filter=Q(Q(sales__created__gte=start_date) & Q(sales__created__lte=end_date))
    ),
    payments_total=ArrayAgg(
        Case(
            When(payments__amount_currency="SUM", then=F('payments__amount') / F('payments__rate')),
            default=F('payments__amount'),
            output_field=FloatField()
        ),
        filter=Q(payments__created__gte=start_date) & Q(payments__created__lte=end_date)
    ),
)

但它返回多个重复的行记录，在这种情况下，它们的总和结果乘以 30；

更新 #1

class Client(BaseSoftDeletableModel):

    fullname = models.CharField(
            max_length=100,

    )
    loan = models.FloatField(
            default=0,

    )
    referal = models.ForeignKey(
            'self',
            on_delete=models.CASCADE,
            null=True,
            blank=True,

    )
    price_type = models.ForeignKey(
            'core.PriceType',
            on_delete=models.SET_NULL,
            null=True,
            blank=True,

    )
    shop = models.ForeignKey(
            'core.Shop',
            on_delete=models.SET_NULL,
            null=True,
            blank=True
    )

这是盈利模式

class Profit(models.Model):
    employee = models.ForeignKey(
        'staff.Employee',
        on_delete=models.CASCADE,
        null=True,
    )
    source = models.ForeignKey(
        ProfitSource,
        on_delete=models.CASCADE,
        null=True,
        blank=True,
    )
    cashbox = models.ForeignKey(
        'pos.CashBox',
        on_delete=models.CASCADE,
        related_name='profits',
    )
    from_cashbox = models.ForeignKey(
        'pos.CashBox',
        on_delete=models.CASCADE,
        related_name='out_expenses',
        null=True,
        blank=True,
    )
    created = models.DateTimeField(
        default=timezone.now,
    )
    convert = models.BooleanField(
        default=False,
    )
    profit_type = models.CharField(
        max_length=5,
        choices=TYPES,
        default=SALE_PROFIT,
    )
    amount = models.DecimalField(
        max_digits=16,
        decimal_places=2,
    )
    amount_currency = models.CharField(
        max_length=10,
        choices=settings.CURRENCY_CHOICES,
        default=settings.CURRENCY_CHOICES[0][0],
    )
    note = models.CharField(
        max_length=500,
        null=True,
        blank=True,
    )
    client = models.ForeignKey(
        'pos.Client',
        on_delete=models.CASCADE,
        related_name='payments',
        null=True,
        blank=True,
    )
    sale = models.ForeignKey(
        'pos.Sale',
        on_delete=models.CASCADE,
        related_name='profits',
        null=True,
        blank=True,
    )
    rate = models.FloatField(
        default=0,
    )
    state = FSMField(
        choices=STATES,
        default=STATE_UNDONE,
        max_length=16,
    )
    done = models.BooleanField(
        default=False,
    )

这是我的模型的结构

Answer 1

从我从您的查询中可以看出，您正在尝试进行多个聚合( ArrayAgg )，这在结果中创建了这种行为。您会在此处找到更多详细信息 https://docs.djangoproject.com/en/3.0/topics/db/aggregation/#combining-multiple-aggregations

为了避免这种情况，您应该使用子查询:

clients = Client.objects.order_by('fullname').annotate(
    loans_total=Subquery(
        Sale.objects.filter(
            created__gte=start_date,
            created__lte=end_date,
            client=OuterRef('pk'),
        ).annotate(loan_sum=Sum('loan')).values('loan_sum')[:1]
    ),
    payments_total=Subquery(
        Profit.objects.filter(
            created__gte=start_date,
            created__lte=end_date,
            client=OuterRef('pk'),
        ).annotate(
            payment_amount=Case(
                When(amount_currency="SUM", then=F('amount') / F('rate')),
                default=F('amount'),
                output_field=FloatField(),
            ),
            payment_total=Sum('payment_amount'),
        ).values('payment_total')[:1]
    ),
)

现在，我没有你的“pos.Sale”模型的结构，但我希望我已经让你走上了正确的道路。