Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:

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.

like image 239
Lee Schmidt Avatar asked Oct 26 '12 18:10

Lee Schmidt


People also ask

Can we join two tables using foreign key?

A table must have exactly one primary key to qualify as relational, but that key can be composed of multiple columns. A foreign key, by contrast, is one or more fields or columns that corresponds to the primary key of another table. Foreign keys are what make it possible to join tables to each other.

Which operators can be used to access the fields in the object by Django ORM?

You use a period (.) to access the fields in the Django ORM object.

What is foreign key relationship in Django?

What is ForeignKey in Django? ForeignKey is a Field (which represents a column in a database table), and it's used to create many-to-one relationships within tables. It's a standard practice in relational databases to connect data using ForeignKeys.


2 Answers

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.

like image 150
Connar Stone Avatar answered Sep 21 '22 11:09

Connar Stone


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') 
like image 42
dokkaebi Avatar answered Sep 20 '22 11:09

dokkaebi