How to dynamically compose an OR query filter in Django?

Question

From an example you can see a multiple OR query filter:

Article.objects.filter(Q(pk=1) | Q(pk=2) | Q(pk=3))

For example, this results in:

[<Article: Hello>, <Article: Goodbye>, <Article: Hello and goodbye>]

However, I want to create this query filter from a list. How to do that?

e.g. [1, 2, 3] -> Article.objects.filter(Q(pk=1) | Q(pk=2) | Q(pk=3))

Viv

You appear to have asked this twice: stackoverflow.com/questions/852404 – Purchasable

For this specific use case you'd probably use Article.objects.filter(pk__in=[1, 2, 3]) in modern django, but the question is still relevant if you want to do something a bit more advanced by OR'ing Q objects together. – Discrepant

Answer 1

You could chain your queries as follows:

values = [1,2,3]

# Turn list of values into list of Q objects
queries = [Q(pk=value) for value in values]

# Take one Q object from the list
query = queries.pop()

# Or the Q object with the ones remaining in the list
for item in queries:
    query |= item

# Query the model
Article.objects.filter(query)

Genesis

Thanks! This was what I was looking for :) Didn't know you could do |= – Viv

You could also initialize the query using: query = Q() – Hagiography

you can make dynamic fields by using **{'fieldname': value}: queries = [Q(**{'fieldname': value}) for value in values] – India

How can you compose raw queries with Django if you want to add optional conditions like above? – Moonstruck

That didn't work for me, I don't know why. queries return zero results for me – Graphy

Answer 2

To build more complex queries there is also the option to use built in Q() object's constants Q.OR and Q.AND together with the add() method like so:

list = [1, 2, 3]
# it gets a bit more complicated if we want to dynamically build
# OR queries with dynamic/unknown db field keys, let's say with a list
# of db fields that can change like the following
# list_with_strings = ['dbfield1', 'dbfield2', 'dbfield3']

# init our q objects variable to use .add() on it
q_objects = Q(id__in=[])

# loop trough the list and create an OR condition for each item
for item in list:
    q_objects.add(Q(pk=item), Q.OR)
    # for our list_with_strings we can do the following
    # q_objects.add(Q(**{item: 1}), Q.OR)

queryset = Article.objects.filter(q_objects)

# sometimes the following is helpful for debugging (returns the SQL statement)
# print queryset.query

Locality

For newcomers to this thread, like myself, I think this answer should be regarded as the top answer. It is more Djangoesque than the accepted answer. Thank you! – Sterrett

I would debate that it is more pythonic to use the builtin OR and AND operators (| and &). q_objects |= Q(pk=item) – Inandin

Perfect! Thank you! – Elegy

Worth noting that if list happens to be empty you'll return the equivalent of Article.objects.all(). Easy to mitigate by returning Article.objects.none() for that test though. – Sensualist

After much googling and tearing my hair out, I found this simple, clean solution. Thank you – Helbonia

@Sensualist you can also initialize q_objects with Q(id__in=[]). It will always fail unless ORed with something and the query optimizer will handle it nicely. – Situs

Answer 3

A shorter way of writing Dave Webb's answer using python's reduce function:

# For Python 3 only
from functools import reduce

values = [1,2,3]

# Turn list of values into one big Q objects  
query = reduce(lambda q,value: q|Q(pk=value), values, Q())  

# Query the model  
Article.objects.filter(query)  

Incudes

Looks like the "builtin" reduce was removed and replaced with functools.reduce. source – Hidie

Thanks @Hidie , fixed. – Incudes

And it's possible to use operator.or_ instead of the lambda. – Marnimarnia

Answer 4

from functools import reduce
from operator import or_
from django.db.models import Q

values = [1, 2, 3]
query = reduce(or_, (Q(pk=x) for x in values))

Trott

Ok, but where does the operator came from? – Heinous

@mpiskore: Same place as every other Python module: you import it. – Trott

funny. that was really my question: in which module/library can I find it? google didn't help much. – Heinous

oh, I thought it was some sort of Django ORM operator. How silly of me, thanks! – Heinous

Answer 5

Maybe it's better to use sql IN statement.

Article.objects.filter(id__in=[1, 2, 3])

See queryset api reference.

If you really need to make queries with dynamic logic, you can do something like this (ugly + not tested):

query = Q(field=1)
for cond in (2, 3):
    query = query | Q(field=cond)
Article.objects.filter(query)

Talapoin

You could also use query |= Q(field=cond) – Inandin

Answer 6

See the docs:

>>> Blog.objects.in_bulk([1])
{1: <Blog: Beatles Blog>}
>>> Blog.objects.in_bulk([1, 2])
{1: <Blog: Beatles Blog>, 2: <Blog: Cheddar Talk>}
>>> Blog.objects.in_bulk([])
{}

Note that this method only works for primary key lookups, but that seems to be what you're trying to do.

So what you want is:

Article.objects.in_bulk([1, 2, 3])

Purchasable

Answer 7

Solution which use reduce and or_ operators to filter by multiply fields.

from functools import reduce
from operator import or_
from django.db.models import Q

filters = {'field1': [1, 2], 'field2': ['value', 'other_value']}

qs = Article.objects.filter(
   reduce(or_, (Q(**{f'{k}__in': v}) for k, v in filters.items()))
)

p.s. f is a new format strings literal. It was introduced in python 3.6

Dismount

Answer 8

In case we want to programmatically set what db field we want to query:

import operator
questions = [('question__contains', 'test'), ('question__gt', 23 )]
q_list = [Q(x) for x in questions]
Poll.objects.filter(reduce(operator.or_, q_list))

Incapacitate

Answer 9

For loop

values = [1, 2, 3]
q = Q(pk__in=[]) # generic "always false" value
for val in values:
    q |= Q(pk=val)
Article.objects.filter(q)

Reduce

from functools import reduce
from operator import or_

values = [1, 2, 3]
q_objects = [Q(pk=val) for val in values]
q = reduce(or_, q_objects, Q(pk__in=[]))
Article.objects.filter(q)

Both of these are equivalent to Article.objects.filter(pk__in=values)

Why Q() is dangerous

It's important to consider what you want when values is empty. Many answers with Q() as a starting value will return everything. Q(pk__in=[]) is a better starting value. It's an always-failing Q object that's handled nicely by the optimizer (even for complex equations).

Article.objects.filter(Q(pk__in=[]))  # doesn't hit DB
Article.objects.filter(Q(pk=None))    # hits DB and returns nothing
Article.objects.none()                # doesn't hit DB
Article.objects.filter(Q())           # returns everything

If you want to return everything when values is empty, you should AND with ~Q(pk__in=[]) to ensure that behaviour:

values = []
q = Q()
for val in values:
    q |= Q(pk=val)
Article.objects.filter(q)                     # everything
Article.objects.filter(q | author="Tolkien")  # only Tolkien

q &= ~Q(pk__in=[])
Article.objects.filter(q)                     # everything
Article.objects.filter(q | author="Tolkien")  # everything

Q() is nothing, not an always-succeeding Q object. Any operation involving it will just drop it completely.

Situs

That was great explaination covering diff scenarios. Thanks – Genitive

Answer 10

You can use the |= operator to programmatically update a query using Q objects.

Noncommittal

Is this documented anywhere? I have been searching for the last 15 minutes, and this is the only thing I can find. – Taxiplane

Like so much in our industry, it is documented on StackOverflow! – Ruelle

Answer 11

This one is for dynamic pk list:

pk_list = qs.values_list('pk', flat=True)  # i.e [] or [1, 2, 3]

if len(pk_list) == 0:
    Article.objects.none()

else:
    q = None
    for pk in pk_list:
        if q is None:
            q = Q(pk=pk)
        else:
            q = q | Q(pk=pk)

    Article.objects.filter(q)

Seamus

You could use q = Q() instead of q = None, then remove the if q is None clause - slightly less efficient but can remove three lines of code. (The empty Q is subsequently merged away when the query is run.) – Ruelle

Answer 12

Another option I wasn't aware of until recently - QuerySet also overrides the &, |, ~, etc, operators. The other answers that OR Q objects are a better solution to this question, but for the sake of interest/argument, you can do:

id_list = [1, 2, 3]
q = Article.objects.filter(pk=id_list[0])
for i in id_list[1:]:
    q |= Article.objects.filter(pk=i)

str(q.query) will return one query with all the filters in the WHERE clause.

Ruelle

Answer 13

Found solution for dynamical field names:

def search_by_fields(value, queryset, search_in_fields):
    if value:
        value = value.strip()

    if value:
        query = Q()
        for one_field in search_in_fields:
            query |= Q(("{}__icontains".format(one_field), value))

        queryset = queryset.filter(query)

    return queryset

Domitian

This is what I was looking for. Thank you! – Partiality

Answer 14

easy..
from django.db.models import Q import you model args = (Q(visibility=1)|(Q(visibility=0)&Q(user=self.user))) #Tuple parameters={} #dic order = 'create_at' limit = 10

Models.objects.filter(*args,**parameters).order_by(order)[:limit]

Hardback