Alex Rivera | Logout

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

Asked 2012-10-26T18:18:14.990
39

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.

Edit
Report

1 Answer

13

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')
answered 2012-10-27T01:25:41.100

Your Answer