将LEFT OUTER JOIN查询转换为Django orm查询/查询 [英] Converting LEFT OUTER JOIN query to Django orm queryset/query
问题描述
class restProdAPI（models。模型）
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.re st_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 |排除| up
使用Django的模型和orm结构进行此工作的最佳方法是什么？ >
我一直在寻找可能的方法来完全加入这两个表，但没有关系，但似乎没有干净或有效的方式来做到这一点。我也试图寻找在django中做左边外连接的方法，但是文档复杂度很低或难以破译。
我知道我可能要使用 Q 对象来做我在那里的or子句。另外我看过关系，看起来像一个 foreignkey（）可能会工作，但我不确定这是否是最好的方法。任何和所有的帮助将不胜感激。谢谢你提前。
** 编辑1 **
到目前为止Todor提供了一个使用INNER JOIN的解决方案。我可能已经找到了一个解决方案 HERE 如果任何人可以解读内嵌原始HTML的混乱。
** 编辑2 **
有没有办法过滤一个字段（where something =something）像我上面给出的查询，Todor的答案？我尝试了以下，但它仍然包括所有记录，即使我的等效的postresql查询正常工作。看来我不能在我所在的地方拥有一切，因为当我删除一个或多个语句时，只需执行一个语句即可应用排除的过滤器。
$ code 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'
}，
表= ['app_restprodapi']，
其中= ['app_restprodapi.rest_mode =％s \
和app_restprodapi.rest_host = app_soapprodapi.soap_host \
或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']，
其中= ['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 一起返回的对象，如果
使用INNER JOIN解决
如果您只能使用 soapProdAPI的，其中包含相应的 restProdAPI （根据您的连接语句 - >由主机或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 我建议仔细阅读文档。一般来说，我们不能使用 .filter 与 select dict中的某些字段，因为它们不是部分的 soapProdAPI 和Django无法解析它们。我们必须坚持在 .extra 中的其中 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'：（从app_restProdAPI中选择rest_id，其中app_restProdAPI.rest_host = app_soapProdAPI.soap_host或app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip），
'rest_host' ：（从app_restProdAPI其中app_restProdAPI.rest_host = app_soapProdAPI.soap_host或app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip选择rest_host），
'rest_ip'：（从app_restProdAPI中选择rest_ip，其中app_restProdAPI.rest_host = app_soapProdAPI.soap_host或app_restProdAPI 。
'rest_mode'：（从app_restProdAPI中选择rest_mode，其中app_restProdAPI.rest_host = app_soapProdAPI.soap_host或app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip），
'rest_state'： （从app_restProdAPI中选择rest_state，其中app_restProdAPI.rest_host = app_soapProdAPI.soap_host或app_sopProdAPI.rest_ip = app_soapProdAPI.soap_ip）
}，
）
Given PostgreSQL 9.2.10, Django 1.8, python 2.7.5 and the following models:
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)
And the following raw query which returns exactly what I am looking for:
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';
Which returns like this:
rest_id | soap_id | rest_host | rest_ip | soap_asset | rest_mode | rest_state
---------+---------+---------------+----------------+------------+-----------+-----------
1234 | 12345 | 1G24019123ABC | 123.123.123.12 | A1234567 | Excluded | Up
What would be the best method for making this work using Django's model and orm structure?
I have been looking around for possible methods for joining the two tables entirely without a relationship but there does not seem to be a clean or efficient way to do this. I have also tried looking for methods to do left outer joins in django, but again documentation is sparse or difficult to decipher.
I know I will probably have to use Q objects to do the or clause I have in there. Additionally I have looked at relationships and it looks like a foreignkey() may work but I am unsure if this is the best method of doing it. Any and all help would be greatly appreciated. Thank you in advance.
** EDIT 1 **
So far Todor has offered a solution that uses a INNER JOIN that works. I may have found a solution HERE if anyone can decipher that mess of inline raw html.
** EDIT 2 **
Is there a way to filter on a field (where something = 'something') like my query above given, Todor's answer? I tried the following but it is still including all records even though my equivalent postresql query is working as expected. It seems I cannot have everything in the where that I do because when I remove one of the or statements and just do a and statement it applies the excluded filter.
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']
)
** EDIT 3 / CURRENT SOLUTION IN PLACE **
To date Todor has provided the most complete answer, using an INNER JOIN, but the hope is that this question will generate thought into how this still may be accomplished. As this does not seem to be inherently possible, any and all suggestions are welcome as they may possibly lead to better solutions. That being said, using Todor's answer, I was able accomplish the exact query I needed:
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 **
I would like to convert this PostGreSQL query to the ORM provided by Django WITHOUT using .raw() or any raw query code at all. I am completely open to changing the model to having a foreignkey if that facilitates this and is, from a performance standpoint, the best method. I am going to be using the objects returned in conjunction with django-datatables-view if that helps in terms of design.
Solving it with INNER JOIN
In case you can go with only soapProdAPI's that contain corresponding restProdAPI ( in terms of your join statement -> linked by host or ip). You can try the following:
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"]
)
How to filter more?
Since we are using .extra I would advice to read the docs carefully. In general we can't use .filter with some of the fields inside the select dict, because they are not part of the soapProdAPI and Django can't resolve them. We have to stick with the where kwarg in .extra, and since it's a list, we better just add another element.
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']
Repeated subquery
If you really need all soapProdAPI's no matter if they have corresponding restProdAPI I can only think of a one ugly example where a subquery is repeated for each field you need.
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)"
},
)
这篇关于将LEFT OUTER JOIN查询转换为Django orm查询/查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
