Django查询以合并两个表的记录 [英] Django query to join records of two tables

问题描述

我正在将django-mssql 1.6.2软件包与django 1.7一起使用，以从sql server 2008中获取表的一个或多个记录。按如下方式调用 get或 filter时，一切都很好，但是我的服务器程序是非常慢。请考虑以下表格：

I am using django-mssql 1.6.2 package with django 1.7 to get one or several records of tables from sql server 2008. When I call "get" or "filter" as follows, everything is fine but my server program is very slow. Consider the following tables:

class Contact(models.Model):
      id = models.IntegerField(primary_key=True, unique=True, null=False)
      address = models.CharField(max_length = 100)
      phone = models.IntegerField(unique=True)

class Parent(models.Model):
      id = models.IntegerField(primary_key=True, unique=True, null=False)
      name = models.CharField(max_length = 50)
      contact = models.OneToOneField(Contact)

class Student(models.Model):
      id = models.IntegerField(primary_key=True, unique=True, null=False)
      name = models.CharField(max_length = 50)
      parent = models.ForeignKey(Parent)

假设我们要打印模板中的表格，例如：

Assume that we want to print a table in a template like:

Student name  |  Parent name  |  Parent phone number
John          |  Jack         |  1111111
Susan         |  Jack         |  1111111
Dan           |  Jack         |  1111111
Jackie        |  Sara         |  2222222

我使用的代码如下：

    query_results = list()
    parents = Parent.objects.all()
    for any_parent in parents:
        students = Student.objects.filter(parent=any_parent)
        for student in students:
            element = TempObjectForStudent()
            element.student_name = student.name
            element.parent_name = any_parent.name
            element.parent_phone = any_parent.contact.phone
            query_results.append(element)

         if students.__len__() == 0:
            element = TempObjectForStudent()
            element.student_name = 'without any active student'
            element.parent_name = any_parent.name
            element.parent_phone = any_parent.contact.phone
            query_results.append(element)

class TempObjectForStudent():
    student_name = None
    parent_name = None
    parent_phone = None

使用以下模板：

{% for row in query_results %}
    <tr><td>{{ row.student_name }}</td><td>{{ row.parent_name }}</td><td>{{ row.parent_phone }}</td></tr>

问题在于使用此方法运行大量查询的速度。

As I mentioned before, the problem is the speed of running lots of queries in this method. How can I use join to deliver all of the table data in just one query?

推荐答案

好吧，您做了一点点工作后，如何使用联接在一个查询中提供所有表数据？

Ok, you made it a little bit too complicated.

不需要TempObjectForStudent。您可以在没有该实例的情况下获取学生实例。只需向您的学生模型的父母字段添加 related_name ，因此您只需使用点符号即可引用 Parent 对象。

There is no need for TempObjectForStudent. You can get the student instance without that. Simply add a related_name to you Student model's parent field, so you can get reference to the Parent object simply using dot notation.

另外请注意，Django提供了每个模型 id 字段自动。您可以在此处阅读更多内容，并此处。因此，无需在模型中显式创建该字段。

Also please note that Django gives each model the id field automaticaly. You can read more here and here. So there is no need to create that field explicitly in your models.

关于您的问题，我会这样做：

Regarding your question, I would do it like this:

# models.py
class Parent(models.Model):
      name = models.CharField(max_length = 50)
      contact = models.OneToOneField(Contact)

class Student(models.Model):
      name = models.CharField(max_length = 50)
      parent = models.ForeignKey(Parent, related_name='students')

# views.py
parents = Parent.objects.all()

# template.html
{% for parent in parents %}
    <tr>
        <td>
            {% with parent.students.all as students %}
                {% for student in students %}
                    {{ student.name }}
                {% empty %}
                    'without any active student'
                {% endfor %}
            {% endwith %}
        </td>
        <td>
            {{ parent.name }}
        </td>
        <td>
            {{ parent.contact.phone }}
        </td>
    </tr>
{% endfor %}

更新：

我遇到了 select_related QuerySet方法。我认为这就是您一直在寻找的！它使用SQL JOIN 子句，并在 SELECT 语句中包括所有相关对象的字段。在此处了解更多信息。希望对您有所帮助！

Update:

I came across a select_related QuerySet method. I think that this is what you've been searching for! It uses SQL JOIN clause and includes all related object's fields in SELECT statement. Read more here. I hope it helps!

这篇关于Django查询以合并两个表的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！