Python 如何在 Django 中进行内部连接？

How to make an Inner Join in django?

提问by Sergio Mendez

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

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

Here is my models.py

这是我的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

Here is my views.py

这是我的 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)

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

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

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

回答by schwobaseggl

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

您可能正在寻找select_related，这是实现这一目标的自然方式：

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

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):

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

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" ) 

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:

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

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

回答by Mehdi

Let me beginning by describing meanings of terms and get order going...

让我从描述术语的含义开始，然后开始……

InnerJoin implies the "common" section between the two (or more) tables. Each one being performed one after the other, as your SQL query suggests.

InnerJoin 意味着两个（或多个）表之间的“公共”部分。正如您的 SQL 查询所建议的那样，一个接一个地执行。

going through your SQL query, you are looking at Publication as the main, all your query are foreign key within publication, giving you ENTIRE dataset.

通过 SQL 查询，您将发布视为主要内容，所有查询都是发布中的外键，为您提供整个数据集。

if my understanding goes correctly what you are looking for in Django equivalent is filter, in chain (not Query), because Q will give you results from each Q separately and join them, while you want results from one applied onto another.

如果我的理解正确的话，你在 Django 中寻找的等价物是过滤器，在链中（不是查询），因为 Q 会分别为你提供每个 Q 的结果并将它们连接起来，而你希望将一个结果应用到另一个。

(I dont know what the dic.update({"plist": mypublications }) does, its not clear.. Solution: country = country.objects.all()# gets all the country in the table of country. country_state = countrystate.objects.all()# get all the countrystate objects city = city.objects.all()# get all city objects

（我不知道 dic.update({"plist": mypublications }) 是做什么的，不清楚.. 解决方案： country = country.objects.all()# 获取国家表中的country_state = countrystate.objects.all()所有国家。 # 获取所有国家/地区对象 city = city.objects.all()# 获取所有城市对象

solution 1: in python3 you need to use __in, this works ok in Python2. and will get you country with anything from table country (not None), table countrystate (not None), table city (not None), so if anything from anyone of them, it would provide. Note: 'None' (python) = 'Null' (SQL Database) Getting all publications that are having anything from the above (practically will bring you anything. publications_list = publication.objects.filter(country = country, countrystate = country_state, city = city)# get if anyone from any of those where 'id' (based on which is object) match, which makes it innerjoin in itself.

解决方案1：在python3中您需要使用__in，这在Python2中可以正常工作。并将为您提供任何来自表国家（不是无），表国家（不是无），表城市（不是无）的国家，所以如果他们中的任何人有任何东西，它都会提供。=“空”（SQL数据库）获取那些具有与上述任何的所有出版物（“无”（蟒蛇）实际上会为你带来什么：注意 publications_list = publication.objects.filter(country = country, countrystate = country_state, city = city)如果从任何的那些人在哪里（基于这是＃得到“身” object) 匹配，这使得它本身成为内部连接。

回答by Bapan Biswas

    from django.db import models

class Course(models.Model):
    name=models.CharField(max_length=10)
    courseid=models.CharField(max_length=30,primary_key=True)
class Enrollment(models.Model):
    course=models.ForeignKey(Course,on_delete=models.CASCADE)
    enrollid=models.CharField(max_length=20)

#Queryset:
k=Enrollment.objects.filter(course__courseid=1).values('id','course__courseid','course__name','enrollid')

print(k.query)
SELECT "app1_enrollment"."id", "app1_enrollment"."course_id", "app1_course"."name", "app1_enrollment"."enrollid" FROM "app1_enrollment" INNER JOIN "app1_course" ON ("app1_enrollment"."course_id" = "app1_course"."courseid") WHERE "app1_enrollment"."course_id" = 1