热门文章
 当前位置：技术文章 > 编程面试题 >

带有多表查询的SQL的Django views.py版本

问题内容：

在Django版本的SQL多表查询中需要一些帮助。该查询使用3个表来检索的餐厅名称，地址Restaurants table和的菜式Cuisinetypes table。所有基于通过URL传递的美食名称和美食ID都存储在Cuisine表中。

型号

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

问题答案：

好吧，这些是一些不清楚的表和字段名称，但是最好的告诉我查询看起来像这样：

(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模型，它实际上就是此M2M的联接表。Django希望联接表具有两个ForeignKey字段，一个字段指向联接的每一侧。通常，它将为您创建此表以节省理智。显然你没有那么幸运。因此，您必须手动将其连接起来。

似乎“ 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。它也知道执行子查询，因为您正在经历M2M关系。

这样就使我们获得等效于以下内容的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涵盖了您。

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

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