CODEWITHSUNDEEP
pythonsqldjango
Jessie A. Morris
Jessie A. Morris

Reputation: 2317

Selecting and sorting on unique foreign key with Django

I have a table called 'speed' which has a foreign key to a user row and an integer field called 'speed'. What I would like to do is get the top speed for unique users. For example:

If speed contained

id   |   speed   |   user_id   |
1    |    40     |     1       |
2    |    50     |     1       |
3    |    10     |     2       |

it would return id's 2 and 3. Is this possible using Django's database API?

Upvotes: 1

Views: 111

Answers (1)

Daniel Roseman
Daniel Roseman

Reputation: 600059

This is not quite what you asked, but gives you the list of user IDs and their associated max speeds:

from django.db.models import Max
Speed.objects.values_list('user').annotate(Max('speed'))

This might be more useful: a list of User objects with their max speeds:

User.objects.all().annotate(Max('speed__speed'))

Edit after comment Well, you don't show where the time is coming from, but assuming that it's another field on the Speed model, it's still fairly simple:

Speed.objects.filter(time__gte=fiveminutesago).values_list('user').annotate(Max('speed'))

See the documentation on ordering of annotate and filter clauses.

Upvotes: 2

Related Questions