Mixing
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.
django foreign-keys django-admin django-queryset
asked Jan 2 at 5:45
Compro PrasadCompro Prasad
469
add a comment |
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.
django foreign-keys django-admin django-queryset
asked Jan 2 at 5:45
Compro PrasadCompro Prasad
469
add a comment |
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.
django foreign-keys django-admin django-queryset
asked Jan 2 at 5:45
Compro PrasadCompro Prasad
469
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.
django foreign-keys django-admin django-queryset
django foreign-keys django-admin django-queryset
asked Jan 2 at 5:45
Compro PrasadCompro Prasad
469
asked Jan 2 at 5:45
Compro PrasadCompro Prasad
469
asked Jan 2 at 5:45
Compro PrasadCompro Prasad
469
asked Jan 2 at 5:45
Compro PrasadCompro Prasad
469
asked Jan 2 at 5:45
Compro PrasadCompro Prasad
469
469
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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)
answered Jan 2 at 6:42
Sergey PugachSergey Pugach
2,4781620
This is almost a correct replacement for what I asked. Given the example in the question, it doesn't seem to get students who haven't made any transactions yet. What can be done in such case?
– Compro Prasad
Jan 2 at 11:48
I got it working usingStudent.objects.annotate(transactions_sum=Sum('studenttransaction__amount')).filter(Q(transactions_sum__lt=total_fees) | Q(studenttransaction=None)).distinct(). Is there a better way?
– Compro Prasad
Jan 3 at 14:32
add a comment |
This query should help you
StudentTransaction.objects.annotate(Sum('amount')).values('student').filter(amount__sum__lte=total_fees)
answered Jan 2 at 7:01
Deepam PatelDeepam Patel
915
1
Author wants to returnStudentqueryset itself, but notvalues. He wrote abote that in description that he doesn't want to usevalues().
– Sergey Pugach
Jan 2 at 7:13
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54001761%2fhow-to-do-group-by-on-a-different-attribute-and-sum-on-a-different-attribute-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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)
answered Jan 2 at 6:42
Sergey PugachSergey Pugach
2,4781620
This is almost a correct replacement for what I asked. Given the example in the question, it doesn't seem to get students who haven't made any transactions yet. What can be done in such case?
– Compro Prasad
Jan 2 at 11:48
I got it working usingStudent.objects.annotate(transactions_sum=Sum('studenttransaction__amount')).filter(Q(transactions_sum__lt=total_fees) | Q(studenttransaction=None)).distinct(). Is there a better way?
– Compro Prasad
Jan 3 at 14:32
add a comment |
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)
answered Jan 2 at 6:42
Sergey PugachSergey Pugach
2,4781620
This is almost a correct replacement for what I asked. Given the example in the question, it doesn't seem to get students who haven't made any transactions yet. What can be done in such case?
– Compro Prasad
Jan 2 at 11:48
I got it working usingStudent.objects.annotate(transactions_sum=Sum('studenttransaction__amount')).filter(Q(transactions_sum__lt=total_fees) | Q(studenttransaction=None)).distinct(). Is there a better way?
– Compro Prasad
Jan 3 at 14:32
add a comment |
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)
answered Jan 2 at 6:42
Sergey PugachSergey Pugach
2,4781620
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)
answered Jan 2 at 6:42
Sergey PugachSergey Pugach
2,4781620
edited Jan 2 at 7:08
answered Jan 2 at 6:42
Sergey PugachSergey Pugach
2,4781620
answered Jan 2 at 6:42
Sergey PugachSergey Pugach
2,4781620
answered Jan 2 at 6:42
Sergey PugachSergey Pugach
2,4781620
2,4781620
This is almost a correct replacement for what I asked. Given the example in the question, it doesn't seem to get students who haven't made any transactions yet. What can be done in such case?
– Compro Prasad
Jan 2 at 11:48
I got it working usingStudent.objects.annotate(transactions_sum=Sum('studenttransaction__amount')).filter(Q(transactions_sum__lt=total_fees) | Q(studenttransaction=None)).distinct(). Is there a better way?
– Compro Prasad
Jan 3 at 14:32
add a comment |
This is almost a correct replacement for what I asked. Given the example in the question, it doesn't seem to get students who haven't made any transactions yet. What can be done in such case?
– Compro Prasad
Jan 2 at 11:48
I got it working usingStudent.objects.annotate(transactions_sum=Sum('studenttransaction__amount')).filter(Q(transactions_sum__lt=total_fees) | Q(studenttransaction=None)).distinct(). Is there a better way?
– Compro Prasad
Jan 3 at 14:32
This is almost a correct replacement for what I asked. Given the example in the question, it doesn't seem to get students who haven't made any transactions yet. What can be done in such case?
– Compro Prasad
Jan 2 at 11:48
This is almost a correct replacement for what I asked. Given the example in the question, it doesn't seem to get students who haven't made any transactions yet. What can be done in such case?
– Compro Prasad
Jan 2 at 11:48
I got it working using
Student.objects.annotate(transactions_sum=Sum('studenttransaction__amount')).filter(Q(transactions_sum__lt=total_fees) | Q(studenttransaction=None)).distinct(). Is there a better way?– Compro Prasad
Jan 3 at 14:32
I got it working using
Student.objects.annotate(transactions_sum=Sum('studenttransaction__amount')).filter(Q(transactions_sum__lt=total_fees) | Q(studenttransaction=None)).distinct(). Is there a better way?– Compro Prasad
Jan 3 at 14:32
add a comment |
This query should help you
StudentTransaction.objects.annotate(Sum('amount')).values('student').filter(amount__sum__lte=total_fees)
answered Jan 2 at 7:01
Deepam PatelDeepam Patel
915
1
Author wants to returnStudentqueryset itself, but notvalues. He wrote abote that in description that he doesn't want to usevalues().
– Sergey Pugach
Jan 2 at 7:13
add a comment |
This query should help you
StudentTransaction.objects.annotate(Sum('amount')).values('student').filter(amount__sum__lte=total_fees)
answered Jan 2 at 7:01
Deepam PatelDeepam Patel
915
1
Author wants to returnStudentqueryset itself, but notvalues. He wrote abote that in description that he doesn't want to usevalues().
– Sergey Pugach
Jan 2 at 7:13
add a comment |
This query should help you
StudentTransaction.objects.annotate(Sum('amount')).values('student').filter(amount__sum__lte=total_fees)
answered Jan 2 at 7:01
Deepam PatelDeepam Patel
915
This query should help you
StudentTransaction.objects.annotate(Sum('amount')).values('student').filter(amount__sum__lte=total_fees)
answered Jan 2 at 7:01
Deepam PatelDeepam Patel
915
answered Jan 2 at 7:01
Deepam PatelDeepam Patel
915
answered Jan 2 at 7:01
Deepam PatelDeepam Patel
915
answered Jan 2 at 7:01
Deepam PatelDeepam Patel
915
915
1
Author wants to returnStudentqueryset itself, but notvalues. He wrote abote that in description that he doesn't want to usevalues().
– Sergey Pugach
Jan 2 at 7:13
add a comment |
1
Author wants to returnStudentqueryset itself, but notvalues. He wrote abote that in description that he doesn't want to usevalues().
– Sergey Pugach
Jan 2 at 7:13
1
1
Author wants to return
Student queryset itself, but not values. He wrote abote that in description that he doesn't want to use values().– Sergey Pugach
Jan 2 at 7:13
Author wants to return
Student queryset itself, but not values. He wrote abote that in description that he doesn't want to use values().– Sergey Pugach
Jan 2 at 7:13
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54001761%2fhow-to-do-group-by-on-a-different-attribute-and-sum-on-a-different-attribute-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
