Order by count of a ForeignKey field?

Question

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?

Answer 1

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.