如何在 Django 中进行内部连接?

2022-01-01 00:00:00 python django orm mysql inner-join

我想在 Html 中显示出版物的城市、州和国家/地区的名称.但它们在不同的表中.

I want to show in an Html the name of the city, state, and country of a publication. But they are in different tables.

这是我的models.py

class country(models.Model):
    country_name = models.CharField(max_length=200, null=True)
    country_subdomain = models.CharField(max_length=3, null=True)
    def __str__(self):
        return self.country_name

class countrystate(models.Model):
    state_name = models.CharField(max_length=200, null=True)
    country = models.ForeignKey(country, on_delete=models.CASCADE, null=True)
    importance = models.IntegerField(null=True)
    def __str__(self):
        return self.state_name

class city(models.Model):
    city_name = models.CharField(max_length=200, null=True)
    countrystate = models.ForeignKey(countrystate, on_delete=models.CASCADE, null=True)
    def __str__(self):
        return self.city_name

class publication(models.Model):
    user = ForeignKey(users, on_delete=models.CASCADE, null=False)
    title= models.CharField(max_length=300, null=True)
    country=models.ForeignKey(country, on_delete=models.CASCADE, null=True)
    countrystate=models.ForeignKey(countrystate, on_delete=models.CASCADE, null=True)
    city=models.ForeignKey(city, on_delete=models.CASCADE, null=True)

    def __str__(self):
        return self.title

这是我的views.py

Here is my views.py

def publications(request):
    mypublications = publication.objects.filter(user_id=request.session['account_id'])
    dic.update({"plist": mypublications })
    return render(request, 'blog/mypublications.html', dic)

在 django 视图中，下一个 sql 查询的等价物是什么?

In a django view, what is the equivalent of the next sql query?

SELECT p.user_id, p.title, c.cuntry_id, c.country_name, s.state_id, s.state_name, y.city_id, y.city_name FROM publication AS p
INNER JOIN country AS c ON c.id = p.country_id
INNER JOIN countrystate AS s ON s.id = p.countrystate_id
INNER JOIN city AS y ON y.id = p.city_id

推荐答案

您可能正在寻找 select_related，这是实现此目的的自然方式:

You are probably looking for select_related, which is the natural way to achieve this:

pubs = publication.objects.select_related('country', 'country_state', 'city')

您可以通过 str(pubs.query) 检查生成的 SQL，这应该会产生以下几行的输出(示例来自 postgres 后端):

You can check the resulting SQL via str(pubs.query), which should result in output along the following lines (the example is from a postgres backend):

SELECT "publication"."id", "publication"."title", ..., "country"."country_name", ...  
FROM "publication" 
INNER JOIN "country" ON ( "publication"."country_id" = "country"."id" ) 
INNER JOIN "countrystate" ON ( "publication"."countrystate_id" = "countrystate"."id" ) 
INNER JOIN "city" ON ( "publication"."city_id" = "city"."id" )

然后将返回的游标值转换为适当的 ORM 模型实例，这样当您遍历这些发布时，您就可以通过它们自己的对象访问相关表的值.但是，这些沿预选前向关系的访问不会导致额外的数据库命中:

The returned cursor values are then translated into the appropriate ORM model instances, so that when you loop over these publications, you access the related tables' values via their own objects. However, these accesses along the pre-selected forward relations will not cause extra db hits:

{% for p in pubs %}
     {{ p.city.city_name}}  # p.city has been populated in the initial query
     # ...
{% endfor %}

相关文章