CODEWITHSUNDEEP
pythondatabasedjango
Besto
Besto

Reputation: 309

How do I do multiple joins and aggregation with django ORM?

Django/Python/ORM noob here! I'm having a hard time wrapping my head around how joins in the ORM work.

Here are my models:

class Courts(models.Model):
    id = models.AutoField(primary_key=True)
    location_name = models.CharField(max_length=100)
    number = models.IntegerField()
    def __unicode__(self):
        return "%s %s %s" % (self.id, self.location_name, self.number)

class Matches(models.Model):
    id = models.AutoField(primary_key=True)
    date = models.DateTimeField()
    court = models.ForeignKey(Courts)
    def __unicode__(self):
        return "%s %s" % (self.id, self.date)

class Participants(models.Model):
    id = models.AutoField(primary_key=True)
    match = models.ForeignKey(Matches)
    userid = models.ForeignKey(User)
    games_won = models.IntegerField()
    def __unicode__(self):
        return "%s %s %s" % (self.id, self.games_won, self.userid)

The first step is to get all of the "participations" into an output 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 ORM script would I write to get this? I'm having a hard time understanding just how a simple join works with the ORM, much less combining 3 tables.

Next I'll want to aggregate the data so that it would eventually look like this:

[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

Does this change the ORM script (in the view) I would write? Is this something I need to incorporate into the view or in the template?

UPDATE:

So, I think I can use select_related(). So I tried Participants.objects.select_related() and I get this SQL statement

SELECT "squash_participants"."id", "squash_participants"."match_id", "squash_participants"."userid_id", "squash_participants"."games_won", "squash_matches"."id", "squash_matches"."date", "squash_matches"."court_id", "squash_courts"."id", "squash_courts"."location_name", "squash_courts"."number", "auth_user"."id", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."password", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."is_superuser", "auth_user"."last_login", "auth_user"."date_joined" FROM "squash_participants" INNER JOIN "squash_matches" ON ("squash_participants"."match_id" = "squash_matches"."id") INNER JOIN "squash_courts" ON ("squash_matches"."court_id" = "squash_courts"."id") INNER JOIN "auth_user" ON ("squash_participants"."userid_id" = "auth_user"."id"

That looks more like what I'm trying to achieve. Now I can't figure out how to get the data out of this into the template.

UPDATE2:

My view looks like this:

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

And my template looks like this:

{% for matches in matches_list %}
    <tr>
        <td>{{ matches.id }}</td> 
        <td>{{ matches.date|date:"d-m-Y" }}</td>
        <td>{{ matches.date|date:"G:i" }}</td>
    </tr>
    {% endfor %}

It properly renders all of the Participants.id s but won't bring in the match ID's, dates or courts for example.

Upvotes: 2

Views: 1789

Answers (1)

Tomasz Zieliński
Tomasz Zieliński

Reputation: 16346

You have a couple of "things" there:

  1. Defining "id" primary fields is unnecessary and therefore confusing, Django generates them automatically
  2. It's a good practice to name your models using singular forms (i.e. Match instead of Matches etc.) - this way you allow Django to provide proper singular vs plural handling. It's also logical, because object of class Match represents a single match, not a number of them - and for multiple matches you use model manager: Match.objects
  3. Your "Participants" model is in fact intermediate ManyToMany table, which you can declare explicitly in "Matches" model like HERE

Now, regarding your JOIN question, I second the approach of S. Lott which I once read somewhere on StackOverflow - you shouldn't think about JOINs in Django because that's not how Django ORM ("O" stands for "object" - and mapping SQL to objects is not a piece of cake) works. You should focus on doing your job, and resort to using JOINs only in justified cases, like performance issues.

Finally, the answer to your question:

Participants.objects.all()

:-)

You might achieve better performace by using select_related(), also you might apply an ordering, if needed, using order_by().

As for the aggregation, others might come with a one-liner solution which I don't see at the moment, but for now you might add this to "Matches" model:

players = models.ManyToManyField(User, through='Participants')

then you can just fetch matches in the simplest way possible:

Matches.objects.all()

and then each element of the returned set will have "players" property.

UPDATE:

To pass your data to template, you simply need to add it to template rendering context:

http://docs.djangoproject.com/en/1.2/ref/templates/api/#rendering-a-context

This can be simplified using render_to_response shortcut function.

Upvotes: 5

Related Questions