How to aggregate with prefetch related and prefetch

Question

I Have two models clinic and ClinicCredits: I want a list of clinics withsum of available balance The Problem is if i use annotate i have to loop for avalibale alance with queryset of cliniccredits :

class Clinic(models.Model):
    """
    clinic model
    """
    user = models.OneToOneField(User, related_name="clinic", primary_key=True, 
     on_delete=models.CASCADE)

    def __str__(self):
        return self.name

    class Meta:
        db_table = 'clinic'




 class ClinicCredits(models.Model):
 """
  credit details for clinic
 """
    clinic = models.ForeignKey(Clinic, on_delete=models.CASCADE, related_name='c_credits')
    credits = models.FloatField('credits', default=0.0)
    balance = models.FloatField('balance', default=0.0, help_text="balance after deduction of credits")

    def __str__(self):
        return self.clinic.name

    class Meta:
       db_table = 'clinic_credits'

here is my query to fetch clinic:

    clinics = Clinic.objects.filter(practice_id__in=user.dietitian.practiceid).order_by(
        'user__date_joined').prefetch_related(Prefetch('c_credits',ClinicCredits.objects.filter(balance__gt=0),'credit_'))

and how can i use aggregate in this condition or is there some oter way to retreive clinic list with their available credits.

Answer 1

You can try like this using annotate(...):

clinics = Clinic.objects.filter(
    practice_id__in=user.dietitian.practiceid
).order_by('user__date_joined').prefetch_related(
    Prefetch(
        'c_credits',
        ClinicCredits.objects.filter(balance__gt=0),
        'credit_'
    )
).annotate(
    total_cred=Sum('c_credits__balance')
)