Как получить разницу между двумя annotate полями в django orm

Проблема заключается в том, что при таком подходе annotate игнорирует одинаковые суммы, а если убрать distinct=True, то будут дублирующие объекты и разница будет не верна.

queryset = PersonalAccount.objects.select_related(
    'apartment', 'apartment__house', 'apartment__section', 'apartment__owner',
).annotate(
    balance=
    Greatest(Sum('cash_account__sum', filter=Q(cash_account__status=True), distinct=True), Decimal(0))
    -
    Greatest(Sum('receipt_account__sum', filter=Q(receipt_account__status=True), distinct=True), Decimal(0))

).order_by('-id')

class PersonalAccount(models.Model):
    objects = None

    class AccountStatus(models.TextChoices):
        ACTIVE = 'active', _("Активен")
        INACTIVE = 'inactive', _("Неактивен")

    number = models.CharField(max_length=11, unique=True)
    status = models.CharField(max_length=8, choices=AccountStatus.choices, default=AccountStatus.ACTIVE)
    apartment = models.OneToOneField('Apartment', null=True, blank=True, on_delete=models.SET_NULL,
                                     related_name='account_apartment')

class CashBox(models.Model):
    objects = None
    number = models.CharField(max_length=64, unique=True)
    date = models.DateField(default=datetime.date.today)
    status = models.BooleanField(default=True)
    type = models.BooleanField(default=True)
    sum = models.DecimalField(max_digits=10, decimal_places=2)
    comment = models.TextField(blank=True)
    payment_items = models.ForeignKey('PaymentItems', blank=True, null=True, on_delete=models.SET_NULL)
    owner = models.ForeignKey(User, blank=True, null=True, on_delete=models.SET_NULL, related_name='owner')
    manager = models.ForeignKey(User, null=True, on_delete=models.SET_NULL, related_name='manager')
    personal_account = models.ForeignKey('PersonalAccount', blank=True, null=True,
                                         on_delete=models.SET_NULL, related_name='cash_account')
    receipt = models.ForeignKey('Receipt', blank=True, null=True, on_delete=models.SET_NULL)

class Receipt(models.Model):
    objects = None

    class PayStatus(models.TextChoices):
        PAID = 'paid', _("Оплачена")
        PARTIALLY_PAID = 'partially_paid', _("Частично оплачена")
        NOT_PAID = 'not_paid', _("Не оплачена")

    number = models.CharField(max_length=64, unique=True)
    date = models.DateField(default=datetime.date.today)
    date_start = models.DateField(default=datetime.date.today)
    date_end = models.DateField(default=datetime.date.today)
    status = models.BooleanField(default=True)
    status_pay = models.CharField(max_length=15, choices=PayStatus.choices, default=PayStatus.NOT_PAID)
    sum = models.DecimalField(max_digits=10, decimal_places=2, default=0.00, blank=True)
    personal_account = models.ForeignKey('PersonalAccount', blank=True, null=True,
                                         on_delete=models.CASCADE, related_name='receipt_account')
    tariff = models.ForeignKey('Tariff', null=True, on_delete=models.CASCADE)
    apartment = models.ForeignKey('Apartment', null=True, on_delete=models.CASCADE,
                                  related_name='receipt_apartment')

Вернуться на верх