全外连接在django [英] Full outer join in django

问题描述

如何使用django QuerySet API在M2M关联芯片上创建一个完整的外连接的查询？

How can I create a query for a full outer join across a M2M relationchip using the django QuerySet API?

这不支持，有一些关于创建我的

It that is not supported, some hint about creating my own manager to do this would be welcome.

已编辑添加：
@ S.Lott：
感谢启蒙。
OUTER JOIN的需要来自应用程序。它必须生成一个报告，显示输入的数据，即使它仍然不完整。
我不知道结果将是一个新的类/模型。你的提示会帮助我很多。

Edited to add: @S.Lott: Thanks for the enlightenment. The need for the OUTER JOIN comes from the application. It has to generate a report showing the data entered, even if it still incomplete. I was not aware of the fact that the result would be a new class/model. Your hints will help me quite a bit.

推荐答案

Django不支持通常的SQL意义上的join支持对象导航。

Django doesn't support "joins" in the usual SQL sense -- it supports object navigation.

请注意，关系连接（内部或外部）创建一个新的类实体。一个在Django中没有定义的。所以没有适当的结果集，因为没有任何类的定义。你可以做的最好的事情就是定义一个元组，它将被包含在None中，用于缺少组合。

Note that a relational join (inner or outer) creates a new "class" of entities. One that doesn't have a definition in Django. So there's no proper "result set" since there's no class definition for the things you get back. The best you can do is define a tuple which will be packed with None's for missing combinations.

左边（或右边）外连接看起来像这样。它创建两个不相交的子集，那些具有关联集合的相关实体，以及那些不相关的组。

A left (or right) outer join looks like this. It creates two disjoint subsets, those who have an associated set of related entities, and those who don't.

for obj in Model1.objects.all():
    if obj.model2_set().count() == 0:
        # process (obj, None) -- no Model2 association
    else:
        for obj2 in obj.model2_set.all():
            # process (obj, obj2) -- the "inner join" result

完全外连接是与关系无关的其余项目的联合。

A "Full" outer join is a union of the remaining items that have no relationships.

for obj2 in Model2.objects.all():
    if obj2.model1_set().count() == 0:
        # process (None, obj2) -- no Model1 association

这个问题总是，你在处理这三个不同的对象子集的这个奇怪的集合？

The issue is always, what processing are you doing with this weird collection of three different subsets of objects?

对象数据库的要点是将处理集中在对象及其关联对象上。

The point of an object database is to focus the processing on the object and it's associated objects.

这个称为关系连接的特殊集合从不在原始对象模型中。这是从两个（或更多）原始对象构建的一类新对象。

The peculiar collection called a "relational join" is never in the original object model. It's a new class of objects built from two (or more) original objects.

更糟的是，外连接创建一个具有多个子类（内连接，左外连接和右外连接）的集合。 意味着的东西是什么？

Worse, outer joins create a collection with multiple subclasses (inner join, left outer join and right outer join). What does that collection of things mean?

等等，它会变得更糟。如果处理包括对缺少的属性的检查（即如果someObj.anObj2属性为None ：我们本质上正在寻找 Model1 没有 Model2 对象关联的项目Ummm ...为什么我们把它们放在外连接中，只能使用 code>语句？为什么不单独处理每个子集的单独查询？

Wait, it can get worse. If the processing includes checks for the missing attributes (i.e. if someObj.anObj2attribute is None: we're essentially looking for Model1 items with no Model2 object associated. Ummm... why did we put those in the outer join, only to filter them using an if statement? Why not just do separate queries amd process each subset properly?

编辑：重新显示不完整的状态，它不是外连接，简单得多，您需要在视图功能中创建一个（或两个）单独的集合，以显示模板。

When you're showing "incomplete" status, it isn't an outer-join at all. It's much simpler. You need to create one (or two) separate collections in your view function for your template to display.

首先，您应该使用状态码，而不是外键的存在或不存在。可选的外键没有原因 - 它们在那里或不存在状态码可以提供有用的意义（不完整，错误，破碎，不适用，待删除等）

First, you should use status codes, not the presence or absence of a foreign key. Optional foreign keys don't have "reasons" -- they're either there or not there. A status code can provide useful shades of meaning ("incomplete", "in error", "broken", "not applicable", "to be deleted", etc.)

errorList1 = Model1.objects.filter( status="Incomplete" )
errorList2 = Model2.objects.filter( status="Incomplete" )

这两个是完整外部连接的两个非连接部分。然后，您可以在模板中显示这两个错误列表，其中包含相应的列标题和状态代码以及所有内容。

These two are the two non-join parts of a full outer join. You can then display these two error lists in your template with appropriate column titles and status codes and everything.

您甚至可以将它们放入单个表中以模拟旧的完整外部连接报告人们曾经看过

You can even put them into a single table to mimic the old full outer join report people used to see

<table>
    <tr><th>Model1</th><th>Model2</th></tr>
    {% for e1 in errorList1 %}
    <tr><td>e1</td><td>NULL</td></tr>
    {% endfor %}
    {% for e2 in errorList2 %}
    <tr><td>NULL</td><td>e2</td></tr>
    {% endfor %}
</table>

看起来像一个完整的外连接报告。没有完整的外连接。

Looks like a full outer join report. Without the full outer join.

这篇关于全外连接在django的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！