CODEWITHSUNDEEP
djangoforeign-keysdjango-admindjango-queryset
Compro Prasad
Compro Prasad

Reputation: 162

How to do group by on a different attribute and sum on a different attribute in Django?

models.py:

class Student(models.Model):
    total_fees = models.PositiveIntegerField(blank=False)
    roll_number = models.PositiveIntegerField(blank=False)

class StudentTransaction(models.Model):
    student = models.ForeignKey(Student, on_delete=models.CASCADE, blank=False)
    amount = models.PositiveIntegerField(blank=False)
    timestamp = models.DateTimeField(auto_now_add=True, blank=True)

admin.py:

class StudentTransactionModelAdmin(admin.ModelAdmin):
    list_display = ['__str__', 'time', 'amount']

    def formfield_for_foreignkey(self, db_field, request, **kwargs):
        if db_field.name == 'student':
            x = StudentTransaction.objects.order_by().annotate(Sum('amount')).filter(amount__sum__lt=student__total_fees)
            kwargs["queryset"] = [i.student for i in x]
        return super().formfield_for_foreignkey(db_field, request, **kwargs)

While doing annotate I want to get all transaction amounts summed up for every student in the function formfield_for_foreignkey. I want the actual Student objects, so this can't be done using values.

To make it simpler, consider that there are 3 Student objects. One of them has made 2 transactions, another one has made 4 transactions, and the third one hasn't made any transactions. The sum of transaction amounts per student doesn't exceed the student's total_fees. The formfield_for_foreignkey should return all Student objects those who haven't paid their fees yet. The condition is:

sum(<all transactions of each student>) is less than <total_fees of that student>

Note: Some details are intentionally removed to keep the code as short as possible. If something is missing or will produce an error, do report it.

Upvotes: 0

Views: 46

Answers (2)

Sergey Pugach
Sergey Pugach

Reputation: 5669

As far as you need to return Students but not StudentTransaction you can do that:

students = Student.objects.annotate(transactions_sum=Sum('studenttransaction__amount')).filter(transactions_sum__lt=total_fees)

If put code to your method it looks like:

def formfield_for_foreignkey(self, db_field, request, **kwargs):
        if db_field.name == 'student':
            students = Student.objects.annotate(transactions_sum=Sum('studenttransaction__amount')).filter(transactions_sum__lt=total_fees)
            kwargs["queryset"] = students
        return super().formfield_for_foreignkey(db_field, request, **kwargs)

Upvotes: 1

Deepam Patel
Deepam Patel

Reputation: 190

This query should help you

StudentTransaction.objects.annotate(Sum('amount')).values('student').filter(amount__sum__lte=total_fees)

Upvotes: 0

Related Questions