django order by aggregate value from non-related table

user_7682970 0
Viewed 49

I have three models, Accrual and Member, and the common field of these two models is register_no, but this field is not a foreign key

class Accrual(models.Model):
  register_no = models.PositiveIntegerField(verbose_name=_('Register No'))
  amount=models.DecimalField(decimal_places=2, max_digits=17, verbose_name=_('Total Amount'))

class Member(models.Model):
    register_no = models.PositiveIntegerField(unique=True, verbose_name=_('Register No'))

class Driver(models.Model):
    register_no = models.PositiveIntegerField(unique=True, verbose_name=_('Register No'))

I want to list the debt each member/driver has. It can be done with @property;

class Member(models.Model):
    register_no = models.PositiveIntegerField(unique=True, verbose_name=_('Register No'))

    @property
    def debt(self):
        ret_val = Accrual.objects.filter(register_no=self.register_no).aggregate(
            debt=Sum('amount'))

        debt = ret_val.get('debt', 0)
        return debt if debt else 0

but I can't use order_by this way. I want to sort each member by debt. How can I solve this problem?

django-rest-framework django-queryset django-orm django-models django
1 Answers

You can use a subquery to add all the related accruals based on the register_no:

from django.db.models import OuterRef, Subquery, Sum


accruals = Accrual.objects.filter(
  register_no=OuterRef('register_no')
).values('register_no').annotate(debt=Sum('amount'))

Member.objects.annotate(
  debt=Subquery(accruals.values('debt'))
).order_by('debt')

which produces this sql:

SELECT "id", "register_no", (
    SELECT SUM(U0."amount") AS "debt" 
    FROM "accrual" U0 
    WHERE U0."register_no" = ("register_no") 
    GROUP BY U0."register_no"
) AS "debt" 
FROM "member" 
ORDER BY "debt" ASC
user_67598440
Related