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

database, django, orm, postgresql, python

Solution

I've been working with django for a while now and I have had a pretty rough time figuring out the table joins, but I think I finally understand and I would like to pass this on to others so they may avoid the frustration that I had with it.

Consider the following model.py:

class EventsMeetinglocation(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=100)
    address = models.CharField(max_length=200)

    class Meta:
        managed = True
        db_table = 'events_meetinglocation'

class EventsBoardmeeting(models.Model):
    id = models.IntegerField(primary_key=True)
    date = models.DateTimeField()
    agenda_id = models.IntegerField(blank=True, null=True)
    location_id = models.ForeignKey(EventsMeetinglocation)
    minutes_id = models.IntegerField(blank=True, null=True)

    class Meta:
       managed = True
       db_table = 'events_boardmeeting'

Here we can see that `location_id` in `EventsBoardmeeting` is a foreign key for the id in `EventsMeetinglocation`. This means that we should be able to query the information in `EventsMeetinglocation` by going through `EventsBoardmeeting`.

Now consider the following views.py:

def meetings(request):
    meetingData = EventsBoardmeeting.objects.all()
    return render(request, 'board/meetings.html', {'data': meetingData })

As stated many times before in may other posts, django takes care of joins automatically. When we query everything in `EventsBoardmeeting` we also get any related information by foreign key as well, But the way that we access this in html is a little different. We have to go through the variable used as the foreign key to access the information associated with that join. For example:

{% for x in data %}
   {{ x.location_id.name }}
{% endfor %}

The above references ALL of the names in the table that were the result of the join on foreign key. `x` is essentially the `EventsBoardmeeting` table, so when we access `x.location_id` we are accessing the foreign key which gives us access to the information in `EventsMeetinglocation`.

Problem

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.

Original source