当前位置：文江博客话题详情

django 中的完全外连接

发布于 2024-07-08 16:57:34 字数 246 浏览 9 评论 0原文

如何使用 django QuerySet API 创建跨 M2M 关系芯片的完整外部联接的查询？

如果不支持，欢迎一些有关创建我自己的经理来执行此操作的提示。

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

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

那小子欠揍 2024-07-15 16:57:34

Django 不支持通常的 SQL 意义上的“连接”——它支持对象导航。

请注意，关系连接（内部或外部）创建一个新的实体“类”。 Django 中没有定义的一种。因此，没有正确的“结果集”，因为没有返回的内容的类定义。您能做的最好的事情就是定义一个元组，该元组将用 None 来填充缺少的组合。

左（或右）外连接看起来像这样。它创建两个不相交的子集，即具有一组关联实体的子集和没有的子集。

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

“完整”外连接是没有关系的剩余项目的并集。

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

问题始终是，您正在对这个由三个不同对象子集组成的奇怪集合进行什么处理？

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

称为“关系连接”的特殊集合从未出现在原始对象模型中。它是由两个（或更多）原始对象构建的新对象类。

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

等等，情况可能会变得更糟。如果处理包括检查缺失的属性（即if someObj.anObj2attribute is None：我们实际上是在寻找没有Model2的Model1项嗯...为什么我们将它们放在外部联接中，只是使用 if 语句来过滤它们？为什么不正确地执行单独的查询并处理每个子集

？显示“不完整”状态，它根本不是外连接。您需要在视图函数中创建一个（或两个）单独的集合以供模板显示

。，而不是外键的存在或不存在。可选的外键没有“原因”——它们要么存在，要么不存在。状态代码可以提供有用的含义（“不完整”、“错误”）。、“已损坏”、“不适用”、“待删除”等）

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

这两个是完整外部联接的两个非联接部分，然后您可以在模板中使用适当的列标题和显示这两个错误列表。状态码和一切。

您甚至可以将它们放入一个表中以模仿人们过去看到的旧的完整外连接报告看起来

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

像完整外连接报告。没有完整的外连接。

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

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?

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?

Edit: 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.

回复收藏 0 原文