How do you join two tables on a foreign key field using django ORM?


Question

Let's assume I have the following models:

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

class PositionStats(models.Model):
    position = models.ForeignKey(Position)
    averageYards = models.CharField()
    averageCatches = models.CharField()

class PlayerStats(models.Model):
    player = models.ForeignKey(Player)
    averageYards = models.CharField()
    averageCatches = models.CharField()

class Player(models.Model):
    name = models.CharField()
    position = models.ForeignKey(Position)

I want to perform the equivalent SQL query using django's ORM:

SELECT *

FROM PlayerStats

JOIN Player ON player

JOIN PositionStats ON PositionStats.position = Player.position

How would I do that with django's ORM? The query isn't exactly correct, but the idea is that I want a single query, using django's ORM, that gives me PlayerStats joined with PositionStats based on the player's position.

1
21
10/26/2012 6:43:06 PM

It isn't one query, but it's pretty efficient. This does one query for each table involved, and joins them in Python. More on prefetch_related here: https://docs.djangoproject.com/en/dev/ref/models/querysets/#prefetch-related

Player.objects.filter(name="Bob").prefetch_related(
        'position__positionstats_set', 'playerstats_set')
10
10/27/2012 1:25:41 AM

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon