Django ORM Filtering inside db functions returning multiple duplicate rows

Question

I have to list all clients and annotate them total amount of loan and payments inside of one sql query. I tried it with next fragment of code:

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

but it returns multiple duplicated records of rows and result of their sum is multiplied by 30 in this case;

UPDATE #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
    )

Here is profit model

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

Here is the structure of my models

Answer 1

From what I can tell from your query, you're trying to have multiple aggregations (ArrayAgg) which is creating the kind of behavior in the results. You'll find some more details here https://docs.djangoproject.com/en/3.0/topics/db/aggregation/#combining-multiple-aggregations

In order to avoid that, you should use subqueries:

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]
    ),
)

Now, I don't have the structure of your "pos.Sale" model, but I hope I've put you on the right path.