如何使用 django ORM 在外键字段上连接两个表? [英] How do you join two tables on a foreign key field using django ORM?

问题描述

假设我有以下模型:

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)

我想使用 django 的 ORM 执行等效的 SQL 查询:

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

我将如何使用 django 的 ORM 做到这一点?查询并不完全正确，但我的想法是我想要一个使用 django 的 ORM 的查询，它根据玩家的位置给我 PlayerStats 与 PositionStats 连接.

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.

推荐答案

我使用 django 已经有一段时间了，我在弄清楚表连接方面遇到了相当大的困难，但我想我终于明白了，我我想把这个转告给其他人，这样他们就可以避免我对它的挫败感.

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.

考虑以下model.py:

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'

这里我们可以看到EventsBoardmeeting中的location_id是EventsMeetinglocation中id的外键.这意味着我们应该可以通过EventsBoardmeeting查询EventsMeetinglocation中的信息.

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.

现在考虑以下views.py:

Now consider the following views.py:

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

正如之前在其他帖子中多次提到的，django 会自动处理连接.当我们在 EventsBoardmeeting 中查询所有内容时，我们也会通过外键获取任何相关信息，但是我们在 html 中访问它的方式有点不同.我们必须通过用作外键的变量来访问与该连接关联的信息.例如:

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

以上引用了表中作为外键连接结果的所有名称.x 本质上是 EventsBoardmeeting 表，因此当我们访问 x.location_id 时，我们正在访问外键，它使我们可以访问 x.location_id 中的信息代码>事件会议地点.

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.

这篇关于如何使用 django ORM 在外键字段上连接两个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！