如何在Django中进行内部联接? [英] How to make an Inner Join in django?

问题描述

我想在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 %}

这篇关于如何在Django中进行内部联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！