Issue
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?
Solution
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
Answered By - Brian Destura
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.