Python 将AND和OR与Q对象相结合的Django查询过滤器不返回预期结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3986071/
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 query filter combining AND and OR with Q objects don't return the expected results
提问by cgaspoz
I try to combine AND and OR in a filter using Q objects. It looks like that the | behave like an AND. This is related to the previous annotate which is run in the same query and not as a subquery.
我尝试使用 Q 对象在过滤器中组合 AND 和 OR 。看起来是这样的| 表现得像一个 AND。这与在同一查询中运行而不是作为子查询运行的先前注释有关。
What is the correct way to handle this with Django?
用 Django 处理这个问题的正确方法是什么？
models.py
模型.py
class Type(models.Model):
name = models.CharField(_('name'), max_length=100)
stock = models.BooleanField(_('in stock'), default=True)
hide = models.BooleanField(_('hide'), default=False)
deleted = models.BooleanField(_('deleted'), default=False)
class Item(models.Model):
barcode = models.CharField(_('barcode'), max_length=100, blank=True)
quantity = models.IntegerField(_('quantity'), default=1)
type = models.ForeignKey('Type', related_name='items', verbose_name=_('type'))
views.py
视图.py
def hire(request):
categories_list = Category.objects.all().order_by('sorting')
types_list = Type.objects.annotate(quantity=Sum('items__quantity')).filter(
Q(hide=False) & Q(deleted=False),
Q(stock=False) | Q(quantity__gte=1))
return render_to_response('equipment/hire.html', {
'categories_list': categories_list,
'types_list': types_list,
}, context_instance=RequestContext(request))
resulting SQL query
结果 SQL 查询
SELECT "equipment_type"."id" [...] FROM "equipment_type" LEFT OUTER JOIN
"equipment_subcategory" ON ("equipment_type"."subcategory_id" =
"equipment_subcategory"."id") LEFT OUTER JOIN "equipment_item" ON
("equipment_type"."id" = "equipment_item"."type_id") WHERE
("equipment_type"."hide" = False AND "equipment_type"."deleted" = False )
AND ("equipment_type"."stock" = False )) GROUP BY "equipment_type"."id"
[...] HAVING SUM("equipment_item"."quantity") >= 1
expected SQL query
预期的 SQL 查询
SELECT
*
FROM
equipment_type
LEFT JOIN (
SELECT type_id, SUM(quantity) AS qty
FROM equipment_item
GROUP BY type_id
) T1
ON id = T1.type_id
WHERE hide=0 AND deleted=0 AND (T1.qty > 0 OR stock=0)
EDIT: I added the expected SQL query (without the join on equipment_subcategory)
编辑：我添加了预期的 SQL 查询（没有在设备子类别上加入）
采纳答案by cgaspoz
OK, no success here or on #django. So I choose to use a raw SQL query to solve this problem...
好的，这里或#django 都没有成功。所以我选择使用原始SQL查询来解决这个问题......
Here the working code:
这里的工作代码：
types_list = Type.objects.raw('SELECT * FROM equipment_type
LEFT JOIN (
SELECT type_id, SUM(quantity) AS qty
FROM equipment_item
GROUP BY type_id
) T1
ON id = T1.type_id
WHERE hide=0 AND deleted=0 AND (T1.qty > 0 OR stock=0)
')
回答by istruble
Try adding parentheses to explicitly specify your grouping? As you already figured out, multiple params to filter()are just joined via AND in the underlying SQL.
尝试添加括号来明确指定您的分组？正如您已经发现的那样，filter() 的多个参数只是通过底层 SQL 中的 AND 连接起来的。
Originally you had this for the filter:
最初你有这个过滤器：
[...].filter(
Q(hide=False) & Q(deleted=False),
Q(stock=False) | Q(quantity__gte=1))
If you wanted (A & B) & (C | D) then this should work:
如果你想要 (A & B) & (C | D) 那么这应该有效：
[...].filter(
Q(hide=False) & Q(deleted=False) &
(Q(stock=False) | Q(quantity__gte=1)))
回答by Paullo
This answer is late but could be helpful to a lot of guys out there.
这个答案很晚，但可能对那里的很多人有所帮助。
[...].filter(hide=False & deleted=False)
.filter(Q(stock=False) | Q(quantity__gte=1))
This will generate something similar to
这将产生类似于
WHERE (hide=0 AND deleted=0 AND (T1.qty > 0 OR stock=0))
回答by Rami Alloush
Here's my example of complicated query, I hope you find it helpful
这是我的复杂查询示例，希望对您有所帮助
or_condition = Q()
and_condition = Q(company=request.user.profile.company)
for field in MyModel._meta.get_fields():
if field.name != 'created_on' and field.name != 'company':
or_condition.add(
Q(**{"{}__icontains".format(field.name): query}), Q.OR)
and_condition.add(or_condition2, Q.AND)
MyModel.objects.filter(and_condition)
The problem with this method is that you get an empty (AND: )case in your or_condition. It doesn't affect the query at all, but it annoys me!
My current solution is as follows
这种方法的问题是你empty (AND: )在你的or_condition. 它根本不影响查询，但它让我很恼火！我目前的解决方案如下
import operator
from functools import reduce
and_condition = Q(company=request.user.profile.company)
or_condition = reduce(operator.or_, (Q(**{"{}__icontains".format(field.name): query})
for field in MyModel._meta.get_fields() if field.name != 'created_on' and field.name != 'company'))
and_condition.add(or_condition, Q.AND)
MyModel.objects.filter(and_condition)
