Django views.py 中多表查询的 SQL 连接版本

Question

需要一些帮助，关于Django中如何进行多表查询。这个查询涉及到3个表，用来获取餐厅的名称和地址，这些信息来自于Restaurants table，而菜系类型则来自于Cuisinetypes table。所有这些都是基于通过URL传递的菜系名称，而菜系的ID则存储在Cuisine表中。

Models.py

class Restaurant(models.Model):
    name = models.CharField(max_length=50, db_column='name', blank=True)
    slugname = models.SlugField(max_length=50, blank=True)
    address = models.CharField(max_length=100, blank=True)
    city = models.ForeignKey('City', related_name="restaurants")
    location = models.ForeignKey('Location', related_name="restaurants")
    hood = models.ForeignKey('Hood', null=True, blank=True, related_name="restaurants")
    listingrole = models.ForeignKey('Listingrole', related_name="restaurants")
    cuisine_types = models.ManyToManyField('Cuisinetype', null=True, blank=True, related_name="restaurants")
    class Meta:
        db_table = 'restaurant'

class City(models.Model):
    name = models.CharField(max_length=50, db_column='city')
    state = models.CharField(max_length=50, blank=True, null=True)
    switch = models.SmallIntegerField(null=True, blank=True, default='1')
    class Meta:
        db_table = 'city'

class Cuisinetype(models.Model):
    name = models.CharField(max_length=50, db_column='cuisine', blank=True) # Field name made lowercase.
    switch = models.SmallIntegerField(null=True, blank=True, default='1')
    class Meta:
        db_table = 'cuisinetype'

class Location(models.Model):
    name = models.CharField(max_length=50, db_column='location', blank=False, null=False)
    city = models.ForeignKey('City', related_name="locations")
    switch = models.SmallIntegerField(null=True, blank=True, default='1')
    class Meta:
        db_table = 'location'

class Hood(models.Model):
    name = models.CharField(max_length=50, db_column='hood')
    city = models.ForeignKey('City', related_name='hoods')
    location = models.ForeignKey('Location', related_name='hoods')
    switch = models.SmallIntegerField(null=True, blank=True, default='1')
    class Meta:
        db_table = 'hood'    

class Listingrole(models.Model):
    id = models.AutoField(primary_key=True, db_column='id')
    name = models.CharField(max_length=50, db_column='listingrole', blank=True) # Field name made lowercase.
    switch = models.SmallIntegerField(null=True, blank=True, default='1')
    class Meta:
        db_table = 'listingrole'

urls.py

url(r'^cuisine/(?P<cuisine>[-\w]+)/$', 'views.cuisinesearch'),

views.py

def cuisinesearch(request, name='unknown'):
name = name.replace('-', ' ').capitalize()
return render_to_response('cuisinesearch.html', 
                          {'cuisinesearch': Restaurant.objects.filter(city_id=8, switch=1, listingrole__in=[1,2,3,4], cuisine_types__name=name)
                          .distinct().prefetch_related("cuisine_types").order_by('listingrole', 'displayorder')[:50] })

HTML

另外，如何正确显示这个查询的结果呢？

{% for restaurant in cuisinesearch %}
<h2>{{ restaurant.name }}</h2>
<div class="location">{{ restaurant.location }}</div>
<h3>Cuisines:</h3>
<ul class="cuisines">{% for ct in restaurant.cuisine_types.all %}
<li>{{ ct.name }}</li>{% endfor %}
</ul>
{% endfor %}

Answer 1

(Restaurant.objects.filter(city=8, cuisine_type__name="Italian").distinct()
    .prefetch_related("cuisine_types"))

好吧，这些表和字段的名字有点不清楚，但我能告诉你，这个查询大概是这样的：

(Restaurant.objects.filter(city=8, 
     cuisine__cuisinetype__cuisine="Italian").distinct().order_by('name')[:20])

不过，除非你被锁定在这个数据库结构里，否则你的模型看起来会更好一些：

class CuisineType(models.Model):
    name = models.CharField(max_length=50)
    class Meta:
        db_table = 'cuisinetype'

class Restaurants(models.Model):
    city = models.ForeignKey("City", null=True, blank=True) # Apparently defined elsewhere. Should be part of location?
    name = models.CharField(max_length=50)
    location = models.ForeignKey("Location", null=True, blank=True) # Apparently defined elsewhere.
    cuisines = models.ManyToManyField(CuisineType)

然后查询会更像这样：

Restaurant.objects.filter(city=8, cuisines__name="Italian").order_by('name')[:20]

---

好的，让我们一步步分析你的查询，假设你的代码没有变化。我们先从子查询开始。

SELECT DISTINCT res_id FROM cuisine 
        JOIN    cuisinetype ON cuisine.cuisineid = cuisinetype.`cuisineid`
        WHERE   cuisinetype.`cuisine` = 'Italian'

我们看看WHERE子句，发现需要一个JOIN。要进行连接，你必须在其中一个连接的模型中声明一个关系字段（Django会添加一个反向关系，我们应该给它命名）。所以我们要把 cuisine.cuisineid 和 `cuisinetype.cuisineid` 对应起来。这些名字真是糟糕。

这是一个多对多的关系，所以我们需要一个 ManyToManyField。看一下 Cuisine 模型，它实际上是这个多对多关系的连接表。Django通常会为你创建这个连接表，以保持逻辑清晰。显然，你没有那么幸运。所以你得手动连接它。

看起来“GID”字段是一个（没用的）记录ID字段，所以我们假设它是一个自增的整数。（为了确认，可以查看CREATE TABLE命令。）现在我们可以把 Cuisine 模型改写成更合理的样子：

class Cuisine(models.Model):
    cuisinegid = models.AutoField(primary_key=True, db_column='CuisineGID')
    cuisineid = models.ForeignKey("Cuisinetype", null=True, 
        db_column='CuisineID', blank=True)
    res_id = models.ForeignKey("Restaurant", null=True, db_column='Res_ID', 
        blank=True)
    class Meta:
        db_table = 'cuisine'

模型名称用引号括起来是因为这些模型还没有定义（它们在文件后面）。现在Django字段名称不一定要和列名称匹配，所以我们把它们改成更易读的名字。记录ID字段通常就叫 id，外键通常根据它们关联的内容命名：

class Cuisine(models.Model):
    id = models.AutoField(primary_key=True, db_column='CuisineGID')
    cuisine_type = models.ForeignKey("CuisineType", null=True, 
        db_column='CuisineID', blank=True)
    restaurant = models.ForeignKey("Restaurant", null=True, db_column='Res_ID', 
        blank=True)
    class Meta:
        db_table = 'cuisine'

好了，我们完成了连接表的定义。在这个过程中，我们也把同样的东西应用到 Cuisinetype 模型上。注意修正后的驼峰命名类名：

class CuisineType(models.Model):
    id = models.AutoField(primary_key=True, db_column='CuisineID')
    name = models.CharField(max_length=50, db_column='Cuisine', blank=True)
    class Meta:
        db_table = 'cuisinetype'

现在我们终于到了 Restaurant 模型。注意这个名字是 单数； 一个对象只代表一条记录。

我注意到它缺少任何 dp_table 或 db_column 的东西，所以我大胆猜测Django会为它创建。这意味着我们可以让它为我们创建 id 字段，并且可以在代码中省略它。（如果不是这样，那我们就像其他模型一样添加它。但你真的不应该有一个可为空的记录ID。）而这就是我们美食类型的 ManyToManyField：

class Restaurants(models.Model):
    city_id = models.ForeignKey(null=True, blank=True)
    name = models.CharField(max_length=50, blank=True)
    location = models.ForeignKey(null=True, blank=True)
    cuisine_types = models.ManyToManyField(CuisineType, through=Cuisine,
        null=True, blank=True)

注意M2M字段的名称是复数，因为这个关系会导致多个记录。

我们还想在这个模型中添加一些东西，就是反向关系的名称。换句话说，就是如何从其他模型回到 Restaurant。我们通过添加 related_name 参数来实现。这些名称通常是相同的。

class Restaurant(models.Model):
    city_id = models.ForeignKey(null=True, blank=True, 
        related_name="restaurants")
    name = models.CharField(max_length=50, blank=True)
    location = models.ForeignKey(null=True, blank=True, 
        related_name="restaurants")
    cuisine_types = models.ManyToManyField(CuisineType, through=Cuisine,
        null=True, blank=True, related_name="restaurants")

现在我们终于准备好了。让我们看看你的查询：

SELECT  restaurants.`name`, restaurants.`address`, cuisinetype.`cuisine`
FROM    restaurants
JOIN    cuisinetype ON cuisinetype.cuisineid = restaurants.`cuisine`
WHERE   city_id = 8 AND restaurants.id IN (
        SELECT DISTINCT res_id FROM cuisine 
        JOIN    cuisinetype ON cuisine.cuisineid = cuisinetype.`cuisineid`
        WHERE   cuisinetype.`cuisine` = 'Italian')
ORDER BY restaurants.`name`
LIMIT 20

因为这是 FROM restaurants，所以我们从这个模型的默认对象管理器 objects 开始：

Restaurant.objects

在这种情况下，WHERE 子句是一个 filter() 调用，所以我们为第一个条件添加它：

Restaurant.objects.filter(city=8)

你可以在这个条件的右侧使用主键值或 City 对象。不过，查询的其余部分会更复杂，因为它需要 JOIN。在Django中，连接看起来就像通过关系字段进行解引用。在查询中，这意味着用双下划线连接相关字段名称：

Restaurant.objects.filter(city=8, cuisine_type__name="Italian")

Django知道要连接哪些字段，因为在 Cuisine 表中声明了这些字段，这些字段通过 through=Cuisine 参数引入到 cuisine_types 中。它也知道要进行子查询，因为你正在通过一个多对多关系。

所以这就得到了相当于SQL的：

SELECT  restaurants.`name`, restaurants.`address`
FROM    restaurants
WHERE   city_id = 8 AND restaurants.id IN (
        SELECT res_id FROM cuisine 
        JOIN    cuisinetype ON cuisine.cuisineid = cuisinetype.`cuisineid`
        WHERE   cuisinetype.`cuisine` = 'Italian')

到这里还差一步。现在我们需要 SELECT DISTINCT，这样就不会得到同一条记录的多个副本：

Restaurant.objects.filter(city=8, cuisine_type__name="Italian").distinct()

而且你需要拉取美食类型以便显示。结果发现，你的查询在这方面效率不高，因为它只获取了连接表的内容，你还需要进一步查询以获取相关的 CuisineType 记录。猜猜看：Django会帮你处理。

Django会运行两个查询：一个像你的查询那样获取连接的ID，另一个获取相关的 CuisineType 记录。然后通过查询结果的访问就不需要再回到数据库了。

最后两件事是排序：

(Restaurant.objects.filter(city=8, cuisine_type__name="Italian").distinct()
    .prefetch_related("cuisine_types").order_by("name"))

还有 LIMIT：

(Restaurant.objects.filter(city=8, cuisine_type__name="Italian").distinct()
    .prefetch_related("cuisine_types").order_by("name")[:20])

这就是你的查询（以及相关查询）被压缩成两行Python代码。请注意，此时查询还没有被执行。你必须把它放在某个地方，比如模板中，才能让它生效：

def cuisinesearch(request, cuisine):
    return render_to_response('cuisinesearch.html', {
        'restaurants': (Restaurant.objects.filter(city=8, 
             cuisine_type__name="Italian").distinct()
             .prefetch_related("cuisine_types").order_by("name")[:20])
        })

模板：

{% for restaurant in cuisinesearch %}
<h2>{{ restaurant.name }}</h2>
<div class="location">{{ restaurant.location }}</div>
<h3>Cuisines:</h3>
<ul class="cuisines">{% for ct in restaurant.cuisine_types.all %}
<li>{{ ct.name }}</li>{% endfor %}
</ul>
{% endfor %}