How do I list the 10 last submissions with distinct user ids in django?

Question

I want to generate a list of the 10 last submissions - but from different users.

What I currently do is this:

unique_users = []
submissions_from_different_users = []
submissions = Submission.objects.all().order_by('-date')

for submission in submissions:
    if len( submissions_from_different_users ) == 10:
        break
    if not submission.user in unique_users:
        submissions_from_different_users.append( submission )
        unique_users.append( submission.user )
return submissions_from_different_users

There has to be a better way to do this - but how?

Answer 1

There are a few approaches.

• If submissions don't happen too often, but you want to display this list very often, you can denormalise the tables, storing the last submission date in the user table. Then it's simply User.objects.all().order_by('-last_submission_date')[:10]. If you're using django.contrib.auth.models.User then it wont be easy to do this.
• If you want to generate it on the fly, it's not so easy. Django can order by columns from other tables, eg User.objects.all().order_by('-submission__date') but you wont get distinct results, because of the join.

For the second approach, the best query I can think of right now is:

User.objects.annotate(last_submission=models.Max('submission__date')).order_by('-last_submission')[:10]

This probably isn't any more efficient than your approach, especially if you have lots of users, but is useful if you only need to get this list once in a while.

Answer 2

Will is on the right track. I'm going to assume submissions with a higher id have been submitted later than submissions with a lesser id:

  users = User.objects.annotate(last_submission_id = models.Max('submission__id')).\
     order_by('-last_submission_id')[:10]

  submissions = Submissions.objects.\
                    filter(id__in = [user.last_submission_id for user in users])