How to perform OR condition in django queryset?

Question

I want to write a Django query equivalent to this SQL query:

SELECT * from user where income >= 5000 or income is NULL.

How to construct the Django queryset filter?

User.objects.filter(income__gte=5000, income=0)

This doesn't work, because it ANDs the filters. I want to OR the filters to get union of individual querysets.

Scissel

Possible duplicate of How do I do an OR filter in a Django query? – Aurora

Answer 1

from django.db.models import Q
User.objects.filter(Q(income__gte=5000) | Q(income__isnull=True))

via Documentation

Suzerainty

It would help if you add a print of object.query so we can relate both ORM and Query output to familiarize with it. BTW great example. – Graphophone

Is it better to use this type of query or perform two separate queries? – Animalism

What if there are some other queries too along with this @lakshman – Tamas

if both queries are same it will return duplicated queries. How to avoid that? – Astray

I just found how to avoid duplicate, after 6 min. Before the query use set() function. like: set(User.objects.filter(Q(income__gte=5000) | Q(income__isnull=True))) – Astray

@OsmanHamashool it is more efficient to append .distinct() so the database removes duplicates, rather than loading them all into Django then deduplicating in python. – Finnougric

Answer 2

Because QuerySets implement the Python __or__ operator (|), or union, it just works. As you'd expect, the | binary operator returns a QuerySet so order_by(), .distinct(), and other queryset filters can be tacked on to the end.

combined_queryset = User.objects.filter(income__gte=5000) | User.objects.filter(income__isnull=True)
ordered_queryset = combined_queryset.order_by('-income')

Update 2019-06-20: This is now fully documented in the Django 2.1 QuerySet API reference. More historic discussion can be found in DjangoProject ticket #21333.

Flory

"undocumented" and "legacy" make me scared. I think it's safer to use the Q object, as detailed in the accepted answer here. – Animism

FYI, order_by() and distinct() can be applied to the piped queryset after they are combined – Baiss

@Baiss thanks. I confirmed this as well. Will edit – Flory

Can the order_by() be applied to each individual queryset and then combined? So that the order for each condition is still maintained? For example, combined_queryset= User.objects.filter(income__gte=5000).order_by('income') | User.objects.filter(income__lt=5000).order_by('-income') ? – Soutache

@noahandthewhale I don't know, but that would be very useful. Try it and let us know! – Flory

@Oatman: | operator is documented. See docs.djangoproject.com/en/2.0/ref/models/querysets: "In general, Q() objects make it possible to define and reuse conditions. This permits the construction of complex database queries using | (OR) and & (AND) operators; in particular, it is not otherwise possible to use OR in QuerySets." I did not check documentation for earlier versions but pipe operator works from Django 1.1.4 at least (just tried). – Homograft

if both queries are same it will return duplicated queries. How to avoid that? – Astray

I just found how to avoid duplicate, after 6 min. Before the query use set() function. like: combined_queryset = set(User.objects.filter(income__gte=5000) | User.objects.filter(income__isnull=True)) – Astray

No @OsmanHamashool, I think this is a bad idea. It uses python's builtin set constructor to deduplicate the queryset from your database. Always use .distinct() on your queryset instead. That will run more efficiently within your database (SQL) and not burden your python process. Next time duckup "django queryset unique" to find the .distinct() method in the Django docs. – Flory

@Flory In my case I got DISTINCT ON fields is not supported by this database backend. So I the python's set works fine for me. – Astray

@OsmanHamashool yea you must not have a challenging throughput problem if you are using a database like MySQL that doesn't support DISTINCT ON. Switching to Postgres will give you ~100x speedup on that part of your app, if you ever do need it to run fast. – Flory

@Flory thanks, there is another down side for using python's set, which is that you can't make ordering to your query sets. I'm planning to switch to postgres now, I will test it again and update the result in here. – Astray

Answer 3

Both options are already mentioned in the existing answers:

from django.db.models import Q
q1 = User.objects.filter(Q(income__gte=5000) | Q(income__isnull=True))

and

q2 = User.objects.filter(income__gte=5000) | User.objects.filter(income__isnull=True)

However, there seems to be some confusion regarding which one is to prefer.

The point is that they are identical on the SQL level, so feel free to pick whichever you like!

The Django ORM Cookbook talks in some detail about this, here is the relevant part:

---

queryset = User.objects.filter(
        first_name__startswith='R'
    ) | User.objects.filter(
    last_name__startswith='D'
)

leads to

In [5]: str(queryset.query)
Out[5]: 'SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login",
"auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name",
"auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff",
"auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user"
WHERE ("auth_user"."first_name"::text LIKE R% OR "auth_user"."last_name"::text LIKE D%)'

and

qs = User.objects.filter(Q(first_name__startswith='R') | Q(last_name__startswith='D'))

leads to

In [9]: str(qs.query)
Out[9]: 'SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login",
 "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name",
  "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff",
  "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user"
  WHERE ("auth_user"."first_name"::text LIKE R% OR "auth_user"."last_name"::text LIKE D%)'

source: django-orm-cookbook

---

Bias

Answer 4

Just adding this for multiple filters attaching to Q object, if someone might be looking to it. If a Q object is provided, it must precede the definition of any keyword arguments. Otherwise its an invalid query. You should be careful when doing it.

an example would be

from django.db.models import Q
User.objects.filter(Q(income__gte=5000) | Q(income__isnull=True),category='income')

Here the OR condition and a filter with category of income is taken into account

Tamas

Answer 5

In order to add the conditions like "OR" or "AND" as we kind of use in sql queries we have this way as an example

from django.db.models import Q
Poll.objects.get(Q(question__startswith='Who'),Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)))

this is equivalent to this sql query

SELECT * from polls WHERE question LIKE 'Who%'
AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')

I hope you are able to understand this properly that the "," is for "AND" operator and "|" is for the "OR" operator used in django.

Airedale

Answer 6

Similarly to @lprsd's answer, you can use or_ from operator library:

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

User.objects.filter(or_(Q(income__gte=5000), Q(income__isnull=True)))

operator.or_ gets two values as arguments. To use three statements (or more) use reduce:

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

User.objects.filter(reduce(or_, Q(income__gte=5000), Q(income__lt=300), Q(income__isnull=True)))

This solution may be very helpful for queries generated dynamically (for example, using custom parameters from frontend input).

Details

operator and functools are python standard libraries, which means you don't need to install them.

As you can see in a docstring of or_ function, for a and b arguments it's

same as a | b.

And reduce:

Apply function of two arguments cumulatively to the items of sequence, from left to right, so as to reduce the sequence to a single value.

Shirleeshirleen