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.

Answer 1

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

via Documentation

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.

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

---

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

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.

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.