将LEFT OUTER JOIN查询转换为Django ORM查询集/查询。

Question

将LEFT OUTER JOIN查询转换为Django ORM查询集/查询。

5

假设有PostgreSQL 9.2.10，Django 1.8，python 2.7.5以及以下模型：

class restProdAPI(models.Model):
    rest_id = models.PositiveIntegerField(primary_key=True)
    rest_host = models.CharField(max_length=20)
    rest_ip = models.GenericIPAddressField(default='0.0.0.0')
    rest_mode = models.CharField(max_length=20)
    rest_state = models.CharField(max_length=20)


class soapProdAPI(models.Model):
    soap_id = models.PositiveIntegerField(primary_key=True)
    soap_host = models.CharField(max_length=20)
    soap_ip = models.GenericIPAddressField(default='0.0.0.0')
    soap_asset = models.CharField(max_length=20)
    soap_state = models.CharField(max_length=20)

以下是能够返回我所需要的内容的原始查询语句：

SELECT
    app_restProdAPI.rest_id, app_soapProdAPI.soap_id, app_restProdAPI.rest_host, app_restProdAPI.rest_ip, app_soapProdAPI.soap_asset, app_restProdAPI.rest_mode, app_restProdAPI.rest_state
FROM
    app_soapProdAPI
LEFT OUTER JOIN
    app_restProdAPI
ON
    ((app_restProdAPI.rest_host = app_soapProdAPI.soap_host)
OR
    (app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip))
WHERE 
    app_restProdAPI.rest_mode = 'Excluded';

这会返回如下结果：

 rest_id | soap_id |   rest_host   |     rest_ip    | soap_asset | rest_mode | rest_state
---------+---------+---------------+----------------+------------+-----------+-----------
   1234  |  12345  | 1G24019123ABC | 123.123.123.12 |  A1234567  | Excluded  |     Up

使用Django的模型和ORM结构，最好的方法是什么？

我一直在寻找可能的方法来完全连接这两个表，但似乎没有干净或高效的方法来做到这一点。我也尝试了寻找在Django中进行左外连接的方法，但是文档稀少或难以理解。

我知道我可能需要使用Q对象来执行其中的or子句。此外，我已经查看了关系，看起来foreignkey()可能有效，但我不确定这是否是最佳方法。任何帮助都将不胜感激。提前谢谢您。

**编辑1**

到目前为止，Todor提供了一个使用INNER JOIN的解决方案。如果有人能解密内联原始HTML的混乱，我可能已经找到了一个解决方案HERE。

**编辑2**

有没有一种方法可以像我上面的查询那样在一个字段上进行筛选（其中某些内容='something'），就像Todor的回答一样？我尝试了以下操作，但它仍然包括所有记录，即使我的等效postresql查询按预期工作。似乎我不能把所有东西都放在where中，因为当我删除其中一个or语句并只执行and语句时，它会应用排除过滤器。

soapProdAPI.objects.extra(
    select = {
        'rest_id'    : 'app_restprodapi.rest_id',
        'rest_host'  : 'app_restprodapi.rest_host',
        'rest_ip'    : 'app_restprodapi.rest_ip',
        'rest_mode'  : 'app_restprodapi.rest_mode',
        'rest_state' : 'app_restprodapi.rest_state'
    },
    tables = ['app_restprodapi'],
    where  = ['app_restprodapi.rest_mode=%s \
               AND app_restprodapi.rest_host=app_soapprodapi.soap_host \
               OR app_restprodapi.rest_ip=app_soapprodapi.soap_ip'],
    params = ['Excluded']
    )

** 编辑 3 / 已有解决方案 **

到目前为止，Todor 提供了最完整的答案，使用 INNER JOIN，但希望这个问题能引起思考，看看是否仍然可以完成。由于这似乎本质上不可能，因此欢迎任何建议，因为它们可能会带来更好的解决方案。话虽如此，使用 Todor 的答案，我能够实现我需要的确切查询：

restProdAPI.objects.extra(
    select = {
        'soap_id'    : 'app_soapprodapi.soap_id',
        'soap_asset' : 'app_soapprodapi.soap_asset'
    },
    tables = ['app_soapprodapi'],
    where  = ['app_restprodapi.rest_mode = %s',
              'app_soapprodapi.soap_host = app_restprodapi.rest_host OR \
               app_soapprodapi.soap_ip   = app_restprodapi.rest_ip'
    ],
    params = ['Excluded']
    )

** TLDR **

我想将这个PostGreSQL查询转换为Django提供的ORM，完全不使用.raw()或任何原始查询代码。如果从性能角度来看这是最好的方法，我完全可以改变模型，使其具有外键。如果这有助于设计，我将与django-datatables-view返回的对象一起使用。

- beardedeagle

你是否应该使用 INNER JOIN 而不是 LEFT JOIN？我可以用 .extra 来展示一种方法，但查询结果更像是在你的例子中使用了 INNER JOIN。 - Todor

@Todor 我非常乐意接受并感激所有符合tldr验收标准的建议。如果认为我的验收标准不合理或错误，请解释原因，我会相应地进行修订。 - beardedeagle

2个回答

2

我觉得这对您可能很有用！实际上，您可以使用Q来构建查询。我在Django shell中尝试了一下，创建了一些数据，并且执行了如下操作：

restProdAPI.objects.filter(Q(rest_host=s1.soap_host)|Q(rest_ip=s1.soap_ip))

其中s1是soapProdAPI。

这是我编写的所有代码，您可以尝试并查看是否能帮助您。

from django.db.models import Q
from core.models import restProdAPI, soapProdAPI

s1 =  soapProdAPI.objects.get(soap_id=1)

restProdAPI.objects.filter(Q(rest_id=s1.soap_id)|Q(rest_ip=s1.soap_ip))

- jeasoft

1

这是一个好的开始，因为它实际上完成了我的未完成部分。现在我只需要弄清楚如何将 soap_id 和 soap_asset 包含在结果对象中，然后让它适用于所有记录。 - beardedeagle

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Todor · Accepted Answer

使用INNER JOIN解决问题

如果您只能使用包含相应的restProdAPI（在连接语句中与soapProdAPI相关联，例如通过主机或IP链接），则可以尝试以下方法：

soapProdAPI.objects.extra(
    select = {
        'rest_id'   : "app_restProdAPI.rest_id",
        'rest_host' : "app_restProdAPI.rest_host",
        'rest_ip'   : "app_restProdAPI.rest_ip",
        'rest_mode' : "app_restProdAPI.rest_mode",
        'rest_state': "app_restProdAPI.rest_state"
    },
    tables = ["app_restProdAPI"],
    where = ["app_restProdAPI.rest_host = app_soapProdAPI.soap_host \
              OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip"]
)

如何进行更多的筛选？

由于我们正在使用.extra，因此我建议仔细阅读文档。一般来说，我们不能在select字典中使用一些字段与.filter一起使用，因为它们不是soapProdAPI的一部分，Django无法解析它们。我们必须坚持使用.extra中的where kwarg，而且由于它是一个列表，因此最好只添加另一个元素。

    where = ["app_restProdAPI.rest_host = app_soapProdAPI.soap_host \
              OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip",
             "app_restProdAPI.rest_mode=%s"
    ],
    params = ['Excluded']

重复的子查询

如果你确实需要所有soapProdAPI，不管它们是否对应着restProdAPI，我只能想到一个非常丑陋的例子，即为每个需要的字段重复执行一个子查询。

soapProdAPI.objects.extra(
    select = {
        'rest_id'   : "(select rest_id from app_restProdAPI where app_restProdAPI.rest_host = app_soapProdAPI.soap_host OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip)",
        'rest_host' : "(select rest_host from app_restProdAPI where app_restProdAPI.rest_host = app_soapProdAPI.soap_host OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip)",
        'rest_ip'   : "(select rest_ip from app_restProdAPI where app_restProdAPI.rest_host = app_soapProdAPI.soap_host OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip)",
        'rest_mode' : "(select rest_mode from app_restProdAPI where app_restProdAPI.rest_host = app_soapProdAPI.soap_host OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip)",
        'rest_state': "(select rest_state from app_restProdAPI where app_restProdAPI.rest_host = app_soapProdAPI.soap_host OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip)"
    },
)