Order by count of a ForeignKey field?

Django

Django Problem Overview


I have a User model and a Submission model. Each Submission has a ForeignKey field called user_submitted for the User who uploaded it.

class Submission(models.Model):
    uploaded_by = models.ForeignKey('User')
class User(models.Model):
    name = models.CharField(max_length=250 )

My question is pretty simple: how can I get a list of the three users with the most Submissions?

I tried creating a num_submissions method on the User model:

def num_submissions(self):
    num_submissions = Submission.objects.filter(uploaded_by=self).count()
    return num_submissions

and then doing:

top_users = User.objects.filter(problem_user=False).order_by('num_submissions')[:3]

But this fails, as do all the other things I've tried. Can I actually do it using a smart database query? Or should I just do something more hacky in the views file?

Django Solutions


Solution 1 - Django

from django.db.models import Count
top_users = User.objects.filter(problem_user=False) \
                .annotate(num_submissions=Count('submission')) \
                .order_by('-num_submissions')[:3]

You didn't mention problem_user in your example model code, but I've left it in assuming that it is a BooleanField on User.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAP257View Question on Stackoverflow
Solution 1 - DjangoBen JamesView Answer on Stackoverflow