Aggregate data using the ORM

Django/Python noob here!

Here are my models:

class Match(models.Model):
    date = models.DateTimeField()
    court = models.ForeignKey(Court)
    players = models.ManyToManyField(User, through='Participant')

class Participant(models.Model):
    match = models.ForeignKey(Match)
    userid = models.ForeignKey(User)
    games_won = models.IntegerField()

class Court(models.Model):
    location_name = models.CharField(max_length=100)
    number = models.IntegerField()

My view is currently:

def index(request):
    matches_list = Participant.objects.all()
    return render_to_response('squash/base_matches.html', {'matches_list': matches_list}, context_instance = RequestContext(request))
    return HttpResponse(output)

My data in the database looks like the following:

[match_id] [date]       [userid]  [games_won]  [court_location_name]  [court_number]
1          01-01-2011   mike      6            Queen                  5
1          01-01-2011   chris     4            Queen                  5
2          01-02-2011   bob       3            Queen                  6
2          01-02-2011   joe       4            Queen                  6
3          01-03-2011   jessie    5            Queen                  2
3          01-03-2011   john      5            Queen                  2

What I want to be able to do is:

[match_id] [date]       [player1] [player2]  [p1wins] [p2wins] [game_winner] [court_location_name]  [court_number]
1          01-01-2011   mike      chris      6        4        mike          Queen                  5
2          01-02-2011   bob       joe        3        4        joe           Queen                  6
3          01-03-2011   jessie    john       5        5        draw          Queen                  2  

Which means I need to group by match_id. I've tried doing something like the following in my template, but it only aggregates the match_id, date and time. I need to be able to format the rest of the data. In a table structure like preposed right before.

{% regroup matches_list by match as matches_group %}
<ul>
{% for event in matches_group %}
    <li>{{ event.grouper }}
    <ul>
        {% for item in blah.list %}
        <li>{{ item.date }}</li>
        {% endfor %}
    </ul>
    </li>
{% endf开发者_开发技巧or %}
</ul>

Any advice here?

UPDATE: I tested with matches_list = Participant.objects.values('match').annotate(total=Count('match')) and I believe this is what gets me the aggregation. However I don't know how to pull the correct fields out of it in the template. For example:

{% for matches in matches_list %}
<p>{{ matches.match.id }}</p>
{% endfor %}

This gives me 3 entries (I can see 3

's) but nothing gets printed out. Not sure what I need to do for {{ matches.match.id }}

Your table showing 'data in the database' doesn't match your models, so I'm a bit confused.

But going by your models, there's no aggregation needed here at all, since you have a matches_won field directly on the Participant model (I assume that's the data you need to display). You can do it simply by iterating through matches_list (formatting omitted for clarity):

{% for match in matches_list %}
    {{ match.id }}
    {{ match.date }}
    {{ match.players.all.0.userid.username }}
    {{ match.players.all.1.userid.username }}
    {{ match.players.all.0.games_won }}
    {{ match.players.all.1.games_won }}
    {{ match.court.location_name }}
    {{ match.court.number }}
{% endfor %}

Edit But I don't understand why you're starting from a list of Participants, and calling that matches_list. Start from a list of matches, then the above code will work.

I'd suggest an alternate data structure. Instead of having two rows per match, have one row per match. Then your data structure is basically what you want as the results 'view'. Obviously, winner would be a calculated column/value.

Then your django models can look like:

class Match(models.Model):
    player_1 = models.ForeignKey(auth.User)
    score_1 = models.PositiveIntegerField(null=True, blank=True)
    player_2 = models.ForeignKey(auth.USer)
    score_2 = models.PositiveIntegerField(null=True, blank=True)
    court = models.ForeignKey(Court)
    date = models.DateField()

    @property
    def winner(self):
        "None means not played yet, or a draw."
        if self.score_1 > self.score_2:
            return self.player_1
        if self.score_1 < self.score_2:
            return self.player_2

    @property
    def is_draw(self):
        return self.score_1 == self.score_2 and self.score_1 is not None


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

class Court(models.Model):
    location = models.ForeignKey(Location)
    number = models.PositiveIntegerField()