Django查询以合并两个表的记录

本文介绍了Django查询以合并两个表的记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在将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!