python 姜戈左加入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1419537/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Django LEFT JOIN?
提问by Glenn Maynard
I have models, more or less like this:
我有模型，或多或少是这样的：
class ModelA(models.Model):
field = models.CharField(..)
class ModelB(models.Model):
name = models.CharField(.., unique=True)
modela = models.ForeignKey(ModelA, blank=True, related_name='modelbs')
class Meta:
unique_together = ('name','modela')
I want to do a query that says something like: "Get all the ModelA's where field name equals to X that have a ModelB model with a name of X OR with no model name at all"
我想做一个查询，内容如下：“获取所有 ModelA，其中字段名称等于 X，其中 ModelB 模型的名称为 X 或根本没有模型名称”
So far I have this:
到目前为止，我有这个：
ModelA.objects.exclude(field=condition).filter(modelsbs__name=condition)
This will get me all the ModelAs that have at least one modelB (and in reality it will ALWAYS be just one) - but if a ModelA has no related ModelBs, it will not be in the result set. I need it to be in the resultset with something like obj.modelb = None
这将使我获得至少具有一个 modelB 的所有 ModelA（实际上它总是只有一个）-但是如果 ModelA 没有相关的 ModelB，它就不会出现在结果集中。我需要它在结果集中有类似 obj.modelb = None 的东西
How can I accomplish this?
我怎样才能做到这一点？
回答by Glenn Maynard
Use Q to combine the two conditions:
使用 Q 组合两个条件：
from django.db.models import Q
qs = ModelA.objects.exclude(field=condition)
qs = qs.filter(Q(modelbs__name=condition) | Q(modelbs__isnull=True))
To examine the resulting SQL query:
要检查生成的 SQL 查询：
print qs.query.as_sql()
On a similar query, this generates a LEFT OUTER JOIN ... WHERE (a.val = b OR a.id IS NULL).
在类似的查询中，这会生成一个 LEFT OUTER JOIN ... WHERE (a.val = b OR a.id IS NULL)。
回答by joeforker
It looks like you are coming up against the 80% barrier. Why not just use .extra(select={'has_x_or_none':'(EXISTS (SELECT ...))'})to perform a subquery? You can write the subquery any way you like and should be able to filter against the new field. The SQL should wind up looking something like this:
看起来您正面临 80% 的障碍。为什么不只是.extra(select={'has_x_or_none':'(EXISTS (SELECT ...))'})用来执行子查询？您可以按照自己喜欢的任何方式编写子查询，并且应该能够针对新字段进行过滤。SQL 应该看起来像这样：
SELECT *,
((EXISTS (SELECT * FROM other WHERE other.id=primary.id AND other.name='X'))
OR (NOT EXISTS (SELECT * FROM other WHERE other.id=primary.id))) AS has_x_or_none
FROM primary WHERE has_x_or_none=1;
回答by Davide Muzzarelli
Try this patch for custom joins: https://code.djangoproject.com/ticket/7231
尝试使用此补丁进行自定义连接：https: //code.djangoproject.com/ticket/7231
回答by S.Lott
LEFT JOIN is a union of two queries. Sometimes it's optimized to one query. Sometimes, it is not actually optimized by the underlying SQL engine and is done as two separate queries.
LEFT JOIN 是两个查询的联合。有时它会针对一个查询进行优化。有时，它实际上并未由底层 SQL 引擎优化，而是作为两个单独的查询完成。
Do this.
做这个。
for a in ModelA.objects.all():
related = a.model_b.set().all()
if related.count() == 0:
# These are the A with no B's
else:
# These are the A with some B's
Don't fetishize about SQL outer joins appearing to be a "single" query.
不要迷恋 SQL 外连接似乎是一个“单一”查询。
