使用QoObject在Django中动态构建复杂查询

Question

我有一些项目的数据库查询（containers）

有一个相关的表，可以定义一些限制（只有顶级org中的限制，只有dept（customer）中的限制，或者只有org中的team（课程）中的限制）

以下是获取对象列表的代码（不工作）：

def get_containers(customer_id: None, course_id: None):
    q_list = (Q(is_private=False), Q(is_active=True))

    if customer_id:
        try:
            customers = Customer.objects
            customer = customers.get(id=customer_id)
        except KeyError:
            return None

        # Second level restriction: may have a customer-id
        customer_q = (
            Q(restrictions__customer__isnull=True)
            | Q(restrictions__customer=customer.id)
        )

        # Third level restriction: may be restricted to a course-code
        course_q = Q(restrictions__course_code__isnull=True)
        if course_id:
            course_q |= Q(restrictions__course_code=course_id)

        # Top level restriction: restricted to org
        restrictions_q = (
            Q(restrictions__organisation=customer.org.id)
            & customer_q 
            & course_q
        )

        q_list = (Q(q_list) | Q(restrictions_q))

    print(f"q_list: {q_list}")
    return Container.objects.filter(q_list)

在此期间，我一直在使用https://docs.djangoproject.com/en/3.0/topics/db/queries/#complex-lookups-with-q（&；引用的https://github.com/django/django/blob/master/tests/or_lookups/tests.py），以及之前询问的django dynamically filtering with q objects作为引用

我尝试了一系列变体，以使if customer_id:块末尾的OR正常工作，但它们都给了我错误：

• q_list = q_list | restrictions_q\nTypeError: unsupported operand type(s) for |: 'tuple' and 'Q'
• q_list = Q(q_list | restrictions_q)\nTypeError: unsupported operand type(s) for |: 'tuple' and 'Q'
• q_list |= restrictions_q\nTypeError: unsupported operand type(s) for |=: 'tuple' and 'Q'
• q_list.add(restrictions_q, Q.OR)\nAttributeError: 'tuple' object has no attribute 'add'

问题：如何创建q_list = q_list OR restrictions_q构造

Answer 1

根据@Ralf的解决方案，我们使用了以下内容：

def get_containers(customer_id = None, course_id = None):
    q_list = Q(is_active=True, restrictions__organisation__isnull=True)

    if customer_id:
        try:
            customers = Customer.objects
            customer = customers.get(id=customer_id)
        except KeyError:
            return None

        q_restrict_org = Q(
            Q(restrictions__organisation__isnull=True) | 
            Q(restrictions__organisation=customer.org.id)
        )
        q_restrict_cust = Q(
            Q(restrictions__customer__isnull=True) |
            Q(restrictions__customer=customer.id)
        )
        q_restrict_course = Q(
            Q(restrictions__course_code__isnull=True) |
            Q(restrictions__course_code=course_id)
        )

        q_list = (
            Q(is_active=True) & Q(
                    q_restrict_org
                    & q_restrict_cust
                    & q_restrict_course
                )
            )

    return Container.objects.filter(q_list)

Answer 2

好的，主要的问题似乎是您有元组和列表，而不是Q对象。例如，该行

q_list = (Q(is_private=False), Q(is_active=True))

将与以下选项中的任一选项一样正确：

q_obj = Q(Q(is_private=False), Q(is_active=True))
q_obj = Q(is_private=False, is_active=True)

---

但也许你的整个方法可以稍加修改

def get_containers(customer_id=None, course_id=None):
    customer_q = Q()
    org_q = Q()
    if customer_id:
        # this may raise Customer.DoesNotExist error
        customer = Customer.objects.get(id=customer_id)

        # Top level restriction: restricted to org
        org_q = Q(restrictions__organisation=customer.org.id)

        # Second level restriction: may have a customer-id
        customer_q = Q(
            Q(restrictions__customer__isnull=True)
            | Q(restrictions__customer=customer.id))

    course_q = Q()
    if course_id:
        # Third level restriction: may be restricted to a course-code
        course_q = Q(
            Q(restrictions__course_code__isnull=True)
            | Q(restrictions__course_code=course_id))

    # apply Q to queryset
    return Container.objects.filter(
        Q(is_private=False, is_active=True) & org_q & customer_q & course_q)

我建议您创建空的Q对象，并仅在满足条件时替换它们（例如，仅当course_id为true时）；如果不满足这些条件，那么它只是保持一个空的Q对象，这意味着一个空的filter子句。这样.filter()调用就更容易编码了

Answer 3

你的错误表明

'tuple' meant you got a row from the table.
'Q' meant query, as in queryset (multiple rows)
and you were trying to merge the 2.

很简单，您的问题是q_列表查询执行不正确。
q_list = restrictions_q.filter(q_list)

这可能是在声明q_list本身时开始的，或者如果不是指restrictions，则必须声明与q_list关联的表/查询。
顺便说一句，为什么您在查询中进行了4种不同的计算？您可以使用Q（）对所有情况进行简单的筛选