Django views.py SQL加入多表查询的版本 [英] Django views.py Version of SQL Join with Multi Table Query

问题描述

需要帮助Django版本的SQL多表查询。该查询使用3个表格从餐厅表和 Cuisinetypes表中的餐饮类型中检索餐厅名称，地址。所有以烹饪名称通过URL和美食ID存储在美食表中。

Need some help with Django version of SQL multi table query. The query is using 3 tables to retrieve restaurant name, address from Restaurants table and Cuisine type from Cuisinetypes table. All based on cuisine name that is passed through URL and the cuisine ID is stored in Cuisine table.

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

还有什么是正确的方式来显示查询？

Also what would be the correct way to display the query?

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

推荐答案

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

Well, those are some unclear table and field names, but best I can tell that query would look something like:

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

但除非您被锁定到该数据库模式中，否则您的模型将看起来更好：

But unless you're locked into that database schema, your models would look better as:

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)

然后查询将更像： / p>

Then the query would be more like:

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

---

好的，我们来看看你的查询，假设没有更改代码。我们将从子查询开始。

---

OK, let's walk through your query, assuming no changes to your code. We'll start with the subquery.

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

我们看看WHERE子句，看我们需要一个JOIN。要进行连接，您必须在其中一个已连接的模型中声明一个关系字段（Django将添加一个反向关系，我们应该命名）。所以我们将匹配 cuisine.cuisineid 与`cuisinetype.cuisineid。这是一个可怕的命名。

We look at the WHERE clause and see we need a JOIN. To do a join, you must declare a relational field in one of the joined models (Django will add a reverse relation, which we should name). So we're matching up cuisine.cuisineid with `cuisinetype.cuisineid. That's some horrible naming.

这是一个多对多关系，所以我们需要一个 ManyToManyField 。好吧，看看 Cuisine 模型，这真的是这个M2M的加入表。 Django期望一个连接表有两个 ForeignKey 字段，一个指向关节的每一边。通常会为您创造出这样的结果来节省理智。显然你不是那么幸运所以你必须手动挂起它。

That's a many-to-many relation, so we need a ManyToManyField. Well, looking at the Cuisine model, it's really the joining table for this M2M. Django expects a joining table to have two ForeignKey fields, one pointing to each side of the joint. Normally it'll create this for you to save sanity. Apparently you're not that lucky. So you have to manually hook it up.

似乎GID字段是记录的（无用的）ID字段，所以我们假设它是自动递增整数。 （可以肯定的是，检查CREATE TABLE命令。）现在我们可以将 Cuisine 模型重写成一个接近正确的东西：

It seems the "GID" field is a (useless) ID field for the record, so let's assume it's auto-increment integer. (To be sure, check the CREATE TABLE commands.) Now we can rewrite the Cuisine model into something approaching sane:

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 ，外键通常以与之相关的命名：

The model names are quoted because the models haven't been defined yet (they're later in the file). Now there's no requirement that the Django field names match the column names, so let's change them to something more readable. The record ID field is usually just named id, and foreign keys are usually named after what they relate to:

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 模型相同的内容。注意修正的骆驼案例类名称：

OK, we're done defining our joint table. While we're at this, let's apply the same stuff to our Cuisinetype model. Note the corrected camel-case class name:

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'

所以我们终于到了我们的餐厅模型。请注意，这个名称是单一的; 一个对象只表示一个记录。

So we finally get to our Restaurant model. Note that the name is singular; an object only represents one record.

我注意到它缺少任何 dp_table 或 db_column 的东西，所以我要出去一个肢体，猜测Django正在创建它。这意味着我们可以让它为我们创建 id 字段，我们可以从我们的代码中省略它。 （如果不是这样，那么我们只需要添加其他模型，但是你真的不应该有一个可空的记录ID。）这就是我们的菜式类型 ManyToManyField life：

I notice that it lacks any dp_table or db_column stuff, so I'm going out on a limb and guessing Django is creating it. That means we can let it create the id field for us and we can omit it from our code. (If that's not the case, then we just add it like with the other models. But you really shouldn't have a nullable record ID.) And this is where our cuisine type ManyToManyField lives:

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领域的名称是复数形式，因为该关系导致多个记录。

Note that the name for the M2M field is plural, since that relation leads to multiple records.

我们将要添加到此模型中的另一件事是反向关系的名称。换句话说，如何从其他机型回到餐厅。我们通过添加 related_name 参数来执行此操作。他们是一样的并不罕见。

One more thing we will want to add to this model is names for the reverse relationships. In other words, how to go from the other models back to Restaurant. We do this by adding related_name parameters. It's not unusual for them to be the same.

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

现在我们终于设置好了。所以我们来看看你的查询：

Now we're finally set. So let's look at your query:

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 ，我们将从该模型的默认对象管理器，对象：

Since this is FROM restaurants, we'll start with that model's default object manager, objects:

Restaurant.objects

在这种情况下， WHERE 子句是一个过滤器（）呼叫，所以我们添加它的第一个术语：

The WHERE clause in this case is a filter() call, so we add it for the first term:

Restaurant.objects.filter(city=8)

您可以拥有主键值o r a City 对象在该术语的右侧。但是，其余的查询变得更加复杂，因为它需要 JOIN 。 Django中的加入就像通过关系字段取消引用。在查询中，这意味着使用双重下划线加入相关字段名称：

You can have wither a primary key value or a City object on the right hand side of that term. The rest of the query gets more complex, though, because it needs the JOIN. A join in Django just looks like dereferencing through the relation field. In a query, that means joining the relevant field names with a double underscore:

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

Django知道要加入的字段，因为它在美食由 through = Cuisine 参数 cuisine_types 。它也知道做一个子查询，因为你正在经历一个M2M关系。

Django knows which fields to join on because that's declared in the Cuisine table which is pulled in by the through=Cuisine parameter in cuisine_types. it also knows to do a subquery because you're going through an M2M relation.

所以我们的SQL相当于：

So that gets us SQL equivalent to:

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 ，所以我们不会得到相同记录的多个副本：

Halfway there. Now we need SELECT DISTINCT so we don't get multiple copies of the same record:

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

你需要拉出烹饪类型进行展示。原来你所拥有的查询效率不高，因为它只能让你进入连接表，你需要运行更多的查询来获取相关的 CuisineType 记录。猜猜是什么：Django已经覆盖了。

And you need to pull in the cuisine types for display. Turns out that the query you have is inefficient there, because it only gets you to the join table and you need to run further queries to get the related CuisineType records. Guess what: Django has you covered.

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

Django将运行两个查询：一个像你的那样获取联合ID，另外一个可以获得相关的 CuisineType 记录。然后通过查询结果访问不需要返回到数据库。

Django will run two queries: one like yours to get the joint IDs, and one more to get the related CuisineType records. Then accesses via the query result don't need to go back to the database.

最后两件事是排序：

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

而$ code> LIMIT （$ 8

And the LIMIT:

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

还有你的查询（和相关查询）打包成两行Python。记住，在这一点上，查询甚至没有被执行。你必须把它放在一些东西，就像一个模板，然后再做任何事情：

And there's your query (and the related query) packed into two lines of Python. Mind you, at this point, the query hasn't even been executed. You have to put it in something, like a template, before it does anything:

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

这篇关于Django views.py SQL加入多表查询的版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！