Dynamically building complex queries in Django using Q-Objects

Question

I have a database query for some items (containers).

There is a related table that could define some restrictions (Only those in a top-level org, only those in a dept (customer) in an org, or just those in a team (course) in a dept)

Here's my (not working) code to get the list of objects:

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)

I've been using https://docs.djangoproject.com/en/3.0/topics/db/queries/#complex-lookups-with-q (& the referenced https://github.com/django/django/blob/master/tests/or_lookups/tests.py), and the previously asked django dynamically filtering with q objects as references during this.

I've tried a bunch of variations to get the OR at the end of the if customer_id: block to work - and they all give me errors:

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'

Question: How to I create the q_list = q_list OR restrictions_q construct?

score 1 · Accepted Answer · answered Oct 01 '20 at 12:53

Well, the main issue seems to be that you have tuples and list instead of Q objects. For example, the line

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

would be correcter like either of these options:

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

But maybe your whole approach can be modified a bit.

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)

I suggest you create empty Q objects and replace them only if the conditions are met (for example, only if course_id is true-ish); if the conditions are not met, then it simply stays an empty Q object, which means an empty filter clause. This way the .filter() call is simpler to code.

one minor niggle - I need `Q(restrictions__course_code__isnull=True)` for all requests, so we don't pick up anything that's for this `customer`, but restricted to a _different_ `course_id` .... I'll have a play with this though.... looks good — CodeGorilla, Oct 02 '20 at 08:26

score 0 · Answer 2 · answered Oct 06 '20 at 11:29

Following from @Ralf's solution, this is what we've used:

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)

Mehdi · Answer 3 · 2021-06-17T19:09:16.607

Your errors posted indicate.

'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.

Quite simply, your problem is the q_list query execution is incorrect.
q_list = restrictions_q.filter(q_list)

This could be at the beginning when you declared q_list itself, or you will have to declare the table/query associated to q_list if you didn't mean restrictions.
Btw, Why have you made 4 different computations in query?, you could have made simply filter for all cases with Q() within it.

Dynamically building complex queries in Django using Q-Objects

3 Answers3