Select the fastest time for each athlete

Question

I'm trying to generate a list of the best result of each athlete, ordered on time.

Currently I'm just throwing out every duplicate result after getting all results and ordering on time.

I have the following two models:

class Athlete(models.Model):
    name = models.CharField(max_length=100)

class Result(models.Model):
    athlete = ForeignKey(Athlete, on_delete=models.CASCADE)
    time = models.DurationField()

Say the Result table looks like this:

athlete         time   
-------         ----
1               15.00
1               14.00
2               16.00
2               18.00
2               13.00
3               12.00

How do I get the fastest time for each athlete and then sort the list on time? Like this:

athlete         time   
-------         ----
3               12.00
2               13.00
1               14.00

Willem Van Onsem · Accepted Answer · 2019-05-09T18:24:15.413

0

You can use annotation for this, like:

from django.db.models import Min

Athlete.objects.annotate(
    time=Min('result__time')
).order_by('time')

The Athlete objects that arise from this queryset will have an extra attribute .time with the minimum time of the related Result objects. We furthermore use a .order_by(..) to then order the results by the time annotation.

Django will process this with a query that looks like:

SELECT athlete.*, MIN(result.time) AS time
FROM athlete
LEFT OUTER JOIN bar ON athlete.id = result.athlete_id
GROUP BY athlete.*
ORDER BY time ASC

In case an Athlete has no related Results, its time will be None.

In case there are Athlete with no related results, and Athletes with related results, it depends on the database where it will order the NULLs (before the ones with values, or after the ones with values). You can resolve that by specifying how to order with NULLs by using nulls_first or nulls_last [Django-doc] in an F expression. For example:

from django.db.models import F, Min

Athlete.objects.annotate(
    time=Min('result__time')
).order_by(F('time').asc(nulls_last=True))

this will order Athletes without any results at the end of the QuerySet.

edited May 09 '19 at 18:24

answered May 09 '19 at 18:14

Willem Van Onsem

443,496
30
428
555

Thank you! If `Result` has extra fields like 'competition', can I filter on that field using this method? So I can create the same list, but for a specific competition. – Joseph Smith May 09 '19 at 18:50
@RubenvanErk: yes, in that case it is `Athlete.filter(result__competition='WC Quidditch').annotate(..)`. But this will filter *out* the athletes that did *not* participate. From Django-2.0 and further, you can use `Min('result__time', filter=Q(result__competiton='WC Quidditch'))`, which will *include* non-participating `Athlete`s. – Willem Van Onsem May 09 '19 at 18:52
Yes that works. I'm now using this queryset in a template. Can I retrieve the competition of the annotated time? – Joseph Smith May 09 '19 at 19:05
@RubenvanErk: I would let the view pass that to the template as well. It is typically *not* considered very good practice to put "business logic" in the "template layer". That is what the view layer is for. – Willem Van Onsem May 09 '19 at 19:08
I understand, maybe it was a poor example. I'm actually creating a list with the best times for each athlete in a particular event and I want to display on what competition the time was set. – Joseph Smith May 09 '19 at 19:13
@RubenvanErk: that makes it more complicated, since basically that means you have to join over a join with the same table. Probably it can be done with a `Subquery`, although I'm not entirely sure. – Willem Van Onsem May 09 '19 at 19:28

Select the fastest time for each athlete

1 Answers1