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

Posted on

Question :

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

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:


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.

Answer #1:

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

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

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 %}
   {{ }}
{% 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.

Answered By: Connar Stone

Answer #2:

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:

        'position__positionstats_set', 'playerstats_set')
Answered By: dokkaebi

Answer #3:

select_related() and prefetch_related() is your solution. They work almost same way but has some difference.

select_related() works by creating an SQL join and including the fields of the related object in the SELECT statement. For this reason, select_related gets the related objects in the same database query. But it only works for one-to-one or one-to-many relation. Example is below-

entry = Entry.objects.select_related('blog').get(id=5)
entries = Entry.objects.filter(foo='bar').select_related('blog')

prefetch_related(), on the other hand, does a separate lookup for each relationship and does the ‘joining’ in Python. This allows it to prefetch many-to-many and many-to-one objects, which cannot be done using select_related. So prefetch_related will execute only one query for each relation. Example is given below-

Answered By: Emdadul Sawon

Answer #4:

From django.db import connection In your view include the below statement:

cursor = connection.cursor()
cursor.execute("select * From Postion ON = Player.position JOIN
PlayerStats ON = 
PlayerStats.player JOIN PositionStats ON = PositionStats.player")
solution = cursor.fetchall()
Answered By: user2610811

Leave a Reply

Your email address will not be published.