1

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?

Kevin Brown-Silva
  • 40,873
  • 40
  • 203
  • 237
Ferguzz
  • 5,777
  • 7
  • 34
  • 41

1 Answers1

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)
jminuscula
  • 552
  • 2
  • 10
  • thanks for your answer. could you elaborate a little on why you would rather do it the second way? what is the performance impact? – Ferguzz Mar 19 '14 at 09:59
  • 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 https://docs.djangoproject.com/en/dev/ref/templates/builtins/#regroup – jminuscula Mar 19 '14 at 22:52