SQL Django 在/不在查询中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4523282/
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 in / not in query
提问by Turbo
I'm trying to figure out how to write a 'not in' style query in django. For example, the query structure I'm thinking of would look like this.
我想弄清楚如何在 Django 中编写“不符合”样式的查询。例如，我正在考虑的查询结构如下所示。
select table1.*
from table1
where table1.id not in
(
select table2.key_to_table1
from table2
where table2.id = some_parm
)
What would the django syntax look like assuming models called table1 and table2?
假设模型名为 table1 和 table2，django 语法会是什么样子？
回答by Harph
table1.objects.exclude(id__in=
table2.objects.filter(your_condition).values_list('id', flat=True))
The exclude function works like the Notoperator you where asking for. The attribute flat = Truetells to table2query to return the value_listas a one level list. So... at the end you are obtaining a list of IDsfrom table2, which you are going to user to define the condition in table1, that will be denied by the exclude function.
exclude 函数的工作方式类似于Not您要求的运算符。该属性flat = True告诉table2查询以value_list一级列表的形式返回。所以...最后，您将获得IDs来自 table2的列表，您将要用户在 中定义条件table1，该列表将被 exclude 函数拒绝。
回答by Sergio Morstabilini
with these models:
使用这些模型：
class table1(models.Model):
field1 = models.CharField(max_length=10) # a dummy field
class table2(models.Model):
key_to_table1 = models.ForeignKey(table1)
you should get what you want using:
你应该得到你想要的使用：
table1.objects.exclude(table2=some_param)
回答by ibz
table1.objects.extra(where=["table1.id NOT IN (SELECT table2.key_to_table1 FROM table2 WHERE table2.id = some_parm)"])
回答by Blairg23
You can write a custom lookup for Django queries:
您可以为 Django 查询编写自定义查找：
From the documentation:"Let's start with a simple custom lookup. We will write a custom lookup newhich works opposite to exact. Author.objects.filter(name__ne='Hyman')will translate to the SQL: "author"."name" <> 'Hyman'"
从文档：“让我们从一个简单的自定义查询，我们将编写一个自定义查找NE其工作相对精确。Author.objects.filter（name__ne =‘Hyman’）将转换为SQL："author"."name" <> 'Hyman'”
from django.db.models import Lookup
class NotEqual(Lookup):
lookup_name = 'ne'
def as_sql(self, compiler, connection):
lhs, lhs_params = self.process_lhs(compiler, connection)
rhs, rhs_params = self.process_rhs(compiler, connection)
params = lhs_params + rhs_params
return '%s <> %s' % (lhs, rhs), params
回答by Blue Peppers
[o1 for o1 in table1.objects.all() if o1.id not in [o2.id for o2 in table2.objects.filter(id=some_parm)]]
Or better
或更好
not_in_ids = [obj.id for obj in table2.objects.filter(id=some_parm)]
selected_objects = [obj for obj in table1.objects.iterator() if obj.id not in not_in_ids]
