django query aggregation grouping with access to all fields

Question

With models defined like so:

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

class Event(models.Model):
    winner = models.ForeignKey(Athlete)
    distance = models.FloatField()

    type_choices = [('LJ', 'Long Jump'), ('HJ', 'High Jump')]
    type = models.CharField(choices=type_choices)

I want to run a query picking out all the events an Athlete has won, grouped by type. I'm currently doing it like so:

athlete = Athlete.objects.get(name='dave')
events_by_type = Events.objects.values('type').annotate(Count('winner')).filter(winner=athlete)

This gives me a dictionary of event types (short versions) and the number of times the athlete has been the winner. However that's all it gives me. If I then want to dig into one of these events to find the distance or even just the verbose type name, I can't.

Am I going about this in the right way? How can I get the events grouped, but also with access to all their fields as well?

jminuscula · Accepted Answer · 2014-03-18 21:41:51Z

1

You are not getting the event instances because you are querying Event.objects with the values method. This will provide you the data for only the specified fields: https://docs.djangoproject.com/en/dev/ref/models/querysets/#values

Performing this kind of group by with the Django ORM is not straightforward. The proposed solution often is:

q = Events.objects.filter(winner=athlete)
q.query.group_by = ['type']
q.count()

But I'd rather do it with straight python. Maybe something like

athlete = Athlete.objects.get(name='dave')
events = Events.objects.filter(winner=athlete)
won_per_type = defaultdict(list)
for e in events:
    won_per_type(e.type).append(e)

answered Mar 18, 2014 at 21:41

jminuscula

5522 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ferguzz Over a year ago

thanks for your answer. could you elaborate a little on why you would rather do it the second way? what is the performance impact?

jminuscula Over a year ago

if you need to access the model instances, the second way to do it is more explicit and readable, and at the same cost regarding the database. It may not be the best option if your event list for an athlete is huge —which I doubt. The latter is also equivalent to what Django itself performs when you group by in the templates docs.djangoproject.com/en/dev/ref/templates/builtins/#regroup

Collectives™ on Stack Overflow

django query aggregation grouping with access to all fields

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related