将AND和OR与Q对象组合在一起的Django查询过滤器不会返回预期的结果 [英] Django query filter combining AND and OR with Q objects don't return the expected results
本文介绍了将AND和OR与Q对象组合在一起的Django查询过滤器不会返回预期的结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我尝试在使用Q对象的过滤器中结合AND和OR。它看起来像那个|表现得像AND一样。这与之前的批注相关,而该注释在同一查询中运行,而不是子查询。
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.
使用Django处理此方法的正确方法是什么?
What is the correct way to handle this with Django?
models.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
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))
生成的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
预期的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)
编辑:我添加了预期的SQL查询(没有在device_subcategory上加入)
EDIT: I added the expected SQL query (without the join on equipment_subcategory)
推荐答案
#django。所以,我选择使用原始的SQL查询来解决这个问题...
OK, no success here or on #django. So I choose to use a raw SQL query to solve this problem...
下面的工作代码:
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)
')
这篇关于将AND和OR与Q对象组合在一起的Django查询过滤器不会返回预期的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文