Django: order by count of a ForeignKey field?

Question

This is almost certainly a duplicate question, in which case apologies, but I've been searching for around half an hour on SO and can't find the answer here. I'm probably using the wrong search terms, sorry.

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 trued 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.