Django model aggregation

Question

I have a simple hierarchic model whit a Person and RunningScore as child. this model store data about running score of many user, simplified something like:

class Person(models.Model):
   firstName = models.CharField(max_length=200)
   lastName = models.CharField(max_length=200)  

class RunningScore(models.Model):
   person = models.ForeignKey('Person', related_name="scores")
   time = models.DecimalField(max_digits=6, decimal_places=2)

If I get a single Person it cames with all RunningScores associated to it, and this is standard behavior. My question is really simple: if I'd like to get a Person with only a RunningScore child (suppose the better result, aka min(time) ) how can I do? I read the official Django documentation but have not found a solution.

score 0 · Accepted Answer · edited Mar 12 '13 at 22:04

I am not 100% sure if I get what you mean, but maybe this will help:

from django.db.models import Min
Person.objects.annotate(min_running_time=Min('time'))

The queryset will fetch Person objects with min_running_time additional attribute.

You can also add a filter:

Person.objects.annotate(min_running_time=Min('time')).filter(firstName__startswith='foo')

Accessing the first object's min_running_time attribute:

first_person = Person.objects.annotate(min_running_score=Min('time'))[0]
print first_person.min_running_time

EDIT:

You can define a method or a property such as the following one to get the related object:

class Person(models.Model):
...
    @property
    def best_runner(self):
        try:
            return self.runningscore_set.order_by('time')[0]
        except IndexError:
            return None

Yes, Your solution it's correct if I need only the best running time but I need the whole Child object and not only "time" field. — Fabrizio, Jun 06 '11 at 08:30
Your solution is was I looking for. I make a little modification: def best_runner(self): — Fabrizio, Jun 07 '11 at 14:39

Facundo Casco · Answer 2 · 2011-06-06T16:46:22.420

0

If you want one RunningScore for only one Person you could use odering and limit your queryset to 1 object. Something like this:

Person.runningscore_set.order_by('-time')[0]

Here is the doc on limiting querysets:

https://docs.djangoproject.com/en/1.3/topics/db/queries/#limiting-querysets

edited Jun 06 '11 at 16:46

answered Jun 06 '11 at 16:13

Facundo Casco

10,065
8
42
63

Django model aggregation

2 Answers2